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

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

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,