Highlight Weekends in a Date List

Excel Formulas › Conditional Formatting

All versionsWEEKDAY

Shade Saturdays and Sundays so a schedule or calendar reads at a glance. A WEEKDAY formula rule flags the weekend — and the same idea highlights any day you choose.

Quick formula: select your date column (A2:A40), then add a formula rule:
=WEEKDAY(A2, 2) > 5
Type 2 makes Monday = 1 … Sunday = 7, so > 5 catches Saturday (6) and Sunday (7).

Functions used (tap for the full reference guide):

WEEKDAY

The example

A week of dates; the weekend rows are shaded.

AB
1DateDay
26/19/2026Fri
36/20/2026Sat
46/21/2026Sun
56/22/2026Mon

The formula

The conditional-formatting rule:

=WEEKDAY(A2, 2) > 5 // Sat & Sun → TRUE

How it works

WEEKDAY turns a date into a day number you can test:

  1. Select the date range, then add a formula rule: =WEEKDAY(A2, 2) > 5.
  2. The second argument 2 sets the numbering to Monday = 1 through Sunday = 7 — clean for “is it the weekend?” math.
  3. Saturday is 6 and Sunday is 7, so > 5 is TRUE only on the weekend.
  4. Pick a fill and click OK. To shade the whole row, select all columns and lock the date column: =WEEKDAY($A2, 2) > 5.

Highlight a specific weekday by matching its number: =WEEKDAY(A2, 2) = 3 flags Wednesdays. To also skip holidays, add a COUNTIF against a holiday list: =OR(WEEKDAY(A2,2)>5, COUNTIF(holidays, A2)).

Try it: interactive demo

Live demo

Pick a start date; a week shows with weekends shaded.

Variations

Whole row

Lock the date column:

=WEEKDAY($A2, 2) > 5

A specific weekday

Highlight Wednesdays:

=WEEKDAY(A2, 2) = 3

Weekends + holidays

Add a holiday list:

=OR(WEEKDAY(A2,2)>5, COUNTIF(holidays, A2))

Pitfalls & errors

Mind the WEEKDAY type. The default type (1) makes Sunday = 1, so >5 would be wrong. Always pass 2 for the Monday-start numbering used here.

Real dates only. Text that looks like a date won’t evaluate — WEEKDAY needs a true date value.

Relative reference. The test cell (A2 or $A2) must match the active cell of your selection or the shading shifts.

Practice workbook

📊
Download the free Highlight Weekends in a Date List practice workbook
A date list with weekend shading, the whole-row, specific-day, and holiday rules applied, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I highlight weekends in Excel?
Select the date column and add a formula rule =WEEKDAY(A2, 2) > 5. The type-2 numbering makes Saturday 6 and Sunday 7, so >5 catches the weekend.
How do I shade the entire weekend row?
Select all columns and lock the date column: =WEEKDAY($A2, 2) > 5.
How do I also highlight holidays?
Combine with COUNTIF against a holiday list: =OR(WEEKDAY(A2,2)>5, COUNTIF(holidays, A2)).

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Working days (holidays) · Weekday name · Highlight dates due

Function references: WEEKDAY