How to highlight weekends in Excel

Dates across the top: the B$1 rule

A staff rota, a project plan or a monthly calendar usually has the dates across a header row and people or tasks down the side. Shading the weekend columns makes the week pattern jump out, and it moves with the dates when you roll the rota forward.

The rota below has dates in B1:H1 and shifts in B2:H4. WEEKDAY(date,2) returns 1 for Monday up to 7 for Sunday, so the test is simply >5. Across the header it gives FALSE, FALSE, TRUE, TRUE, FALSE, FALSE, FALSE: Saturday 28 and Sunday 29 November.

1. Select the whole block, starting from the top-left date: B1:H4.
2. Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
3. Enter =WEEKDAY(B$1,2)>5
4. Click Format, pick a light fill on the Fill tab, and click OK twice.

Why the $ goes before the 1, and the blank-cell trap

The formula is written for the top-left cell of your selection, and Excel shifts it for every other cell. B$1 locks the row but not the column, so every cell in column D looks at D1, every cell in column E looks at E1, and whole columns light up. With $B$1, every cell looks at Thursday 26 November and nothing is shaded. With plain B1, row 2 looks at B2 ("Off"), WEEKDAY returns #VALUE!, and conditional formatting treats an error as "don't format".

Now the trap. If your selection runs past the last date, the empty header cells get shaded too. Excel treats a blank cell as 0, day zero of its calendar, and WEEKDAY calls that a Saturday: =WEEKDAY(J1,2) on an empty cell returns 6. Use =AND(B$1<>"",WEEKDAY(B$1,2)>5) so blank columns stay white. That's handy for a template where next month has fewer days.

For a list with dates down column A instead, lock the column: =WEEKDAY($A2,2)>5 shades whole rows. weekday-or-weekend-in-excel shows that layout with a helper column too.

Shade holidays and closed days too

Weekends are rarely the only days off. List the closed days in a column (K2:K3 here) and add a COUNTIF test, which returns 1 or more when the header date is in the list. Thursday 26 November is in the list, so its column is shaded even though it's a weekday.

If holidays should get a different colour, make them a second rule instead of combining them. Home > Conditional Formatting > Manage Rules lists the rules in order, and when two rules set the same fill, the one higher in the list wins. Put the holiday rule on top.

Friday-Saturday weekends and one day off

Not every team's weekend is Saturday and Sunday. WEEKDAY without a return type numbers Sunday 1 to Saturday 7, so =WEEKDAY(B$1)>5 matches Friday (6) and Saturday (7), the weekend in many Middle Eastern countries. To shade Sundays only, use =WEEKDAY(B$1)=1. For any other pattern, test the days you want with OR, for example =OR(WEEKDAY(B$1)=1,WEEKDAY(B$1)=4) for Sundays and Wednesdays.

Pick one numbering and stick with it. Mixing return type 2 (Monday 1) in one rule with the default (Sunday 1) in another is the usual reason a rule shades the wrong column.

Header dates typed as text never match

The last header, I1, was typed as "Thu 3". It looks like a date but it's text: =ISNUMBER(I1) is FALSE and WEEKDAY(I1,2) returns #VALUE!, so the rule silently skips that column. This is the most common reason a weekend rule "works for some columns".

Type or calculate real dates and change only how they look. Select the header, press Ctrl+1, choose Custom and type ddd d. The cells still hold dates, so the rule works, and they show as Thu 26, Fri 27, Sat 28. To roll the rota forward, put the first date in B1 and =B1+1 in C1, then fill right.

Once the dates are real, you can count weekend work too. =SUMPRODUCT((WEEKDAY(B1:H1,2)>5)*(B2:H4<>"Off")) returns 4 weekend shifts worked, which helps when you share weekends fairly across the team.