Author: Oscar Cronquist Article last updated on November 07, 2018

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. Click the "Conditional Formatting" button.
  4. Click "New Rule.." to open a dialog box.
  5. Click "Use a formula to determine which cells to format".
  6. Type the formula. (See above).
  7. Click "Format..." button and choose a formatting.
  8. Click 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