Author: Oscar Cronquist Article last updated on February 01, 2019

The image above shows conditional formatting applied to cell range C8:C20, it highlights cells containing dates that fall on Saturdays and Sundays.

Conditional formatting formula

=WEEKDAY(C3,2)>5

How to apply conditional formatting to cell range C3:C20

  1. Select cell range C3:C20.
  2. Go to tab "Home" on the ribbon.
  3. Press with left mouse button on the "Conditional Formatting" button.
  4. Press with left mouse button on "New Rule.." to open a dialog box.
  5. Press with left mouse button on "Use a formula to determine which cells to format".
  6. Type the formula. (See above).
  7. Press with left mouse button on "Format..." button and choose a formatting.
  8. Press with left mouse button on OK button twice.

Explaining conditional formatting formula

Step 1 - Identify day of  the week

The WEEKDAY function returns 1 to 7 based on a date's day of the week, the second argument tells the function which day a week begins on. Using 2 as the second argument makes the week start on a Monday.

WEEKDAY(C3,2)

becomes

WEEKDAY(43101,2)

and returns 1.

The number calculated by the WEEKDAY function tells us which day of week a date is:

1 - Monday
2 - Tuesday
3 - Wednesday
4 - Thursday
5 - Friday
6 - Saturday
7 - Sunday

Step 2 - Check if the number is larger than 5

The larger than sign checks if the weekday function is larger than 5. If the number is larger than 5 then it must be a Saturday or Sunday, see list above.

WEEKDAY(C3,2)>5

becomes

1>5 and returns FALSE.

Step 3 - Boolean value determines if the cell is highlighted

If the logical expression returns TRUE then the cell is highlighted and FALSE nothing happens.

How to highlight record when a date falls on a weekend

Conditional formatting formula applied to cell range B3:D20

=WEEKDAY($B3,2)>5

This formula is almost as the first formula, except there is a dollar sign in front of the B (column number). This makes the column absolute meaning the cell reference doesn't change when the Conditional Formatting moves to the next column. It only changes when it moves to a new row.

Example,

Cell Formula
B3 =WEEKDAY($B3,2)>5
C3 =WEEKDAY($B3,2)>5
D3 =WEEKDAY($B3,2)>5
B4 =WEEKDAY($B4,2)>5
C4 =WEEKDAY($B4,2)>5
D4 =WEEKDAY($B4,2)>5

Weekly Blog EMAIL

Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles