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(A2)=2
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.