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

  • Convert Date Into Week Number In VBA

    convert a date into week number in vba, how to get week number from today’s date, vba to convert date into week number, excel date to week number

    Share The Knowledge
  • Hide Error In Pivot Table In Excel

    Pivot table comes to rescue when we want some quick aggregation in a short notice.We could quickly summarize our data and […]

    Share The Knowledge
  • Remove Sum Of From Pivot Table In Excel

    In pivot  when we aggregate sum values we get a little prefix “Sum of” in the column name which sometimes […]

    Share The Knowledge
  • Put Value Within Quotes In Excel

    The post demonstrates how to put a value within a quotation mark in excel. Let’s see a scenario where we […]

    Share The Knowledge

Leave a Reply