How to highlight first Monday of the month in Excel table

Let’s have a list of dates or some kind of calendar. We want to highlight first Mondays or Tuesdays or Wednesdays etc. in each month. The example shows how to use conditional formatting to solve this task.

Highlighting the cell

Select cells with dates and go to Conditional Formatting >>  New Rule… >> Use a formula to determine which cells to format. Now write the formula and choose Format.

formula in conditional formatting

Note: You can also select only one cell and copy conditional formatting from it to other cells (using Format Painter).

Formula for first Monday of the month

=IF(AND(WEEKDAY(A1,2)=1,DAY(A1)<7),TRUE(),FALSE())

Formula for Tuesday

=IF(AND(WEEKDAY(A1,2)=2,DAY(A1)<7),TRUE(),FALSE())

Formula for Wednesday

=IF(AND(WEEKDAY(A1,2)=3,DAY(A1)<7),TRUE(),FALSE())

Formula for Thursday

=IF(AND(WEEKDAY(A1,2)=4,DAY(A1)<7),TRUE(),FALSE())

Formula for Friday

=IF(AND(WEEKDAY(A1,2)=5,DAY(A1)<7),TRUE(),FALSE())

Formula for Saturday

=IF(AND(WEEKDAY(A1,2)=6,DAY(A1)<7),TRUE(),FALSE())

Formula for Sunday

=IF(AND(WEEKDAY(A1,2)=7,DAY(A1)<7),TRUE(),FALSE())

Note that the formulas differ in only one number.

Highlighting the whole row or column

Now we do small modifications in conditional formatting formula to highlight row and column. Place the $ character is very important. There are the examples for Monday. Other days are similar, just change number in formula like before.

Row / first Monday

first monday highlighted whole row of table

=IF(AND(WEEKDAY($A2,2)=1,DAY($A2)<7),TRUE(),FALSE())

Column / first Monday

first monday highlighted whole column of the table

=IF(AND(WEEKDAY(B$1,2)=1,DAY(B$1)<7),TRUE(),FALSE())

Video

This video shows how to set conditional formatting for highlighting the row of table.

Add a comment »One comment to this article

  1. Is there link to download this solution in Excel format?

    Reply



Copyright © All Rights Reserved · Green Hope Theme by Sivan & schiy · Proudly powered by WordPress