Highlight Mondays in Excel

Highlighting the days in excel is a frequent operation.We come across several situation in which we need to highlight some specific days for billing purpose or to mark the beginning or end of period etc.

Let’s see a simple way by which we could highlight all the Mondays in excel from a given date.

There is a date column shown below in which we would like to highlight the Mondays.

We will write a simple formula using WEEKDAY function in excel to achieve the goal here.Weekday function in excel gives the an integer ranging from 1 to 7 that corresponds to a day in a week.


Step 1

Select the data as shown below and go to New Rule as shown below.


Step 2

Select the last option “Use a formula to determine which cells to format” and enter the formula below


WEEKDAY function in excel returns the integer ranging from 1 to 7 representing days of the week with 1 representing Sunday and 7 representing Saturday.

So it will highlight all the Mondays if the function returns 2.

Select a color you want the Mondays to be highlighted, I have used Yellow to highlight by clicking on the format options below the formula box.


Step 3

As you could see, the dates representing Mondays have been highlighted. You could change the value “2” to any number to highlight any day of the week i.e. 3 for Tuesday and so on.


Hope this helped.

Share The Knowledge

Random Posts

Leave a Reply