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. 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