Excel + Google Sheets · Highlight weekends
Make weekends pop out
WEEKDAY with 2 counts Monday as 1, so 6 and 7 are the weekend.
The formula
=WEEKDAY($A2, 2)>5What each part means
WEEKDAY($A2, 2)Day number, Monday = 1>5Saturday or Sunday
Example
Home → Conditional formatting → New rule → Use a formula
| A | B | |
|---|---|---|
| 1 | Date | Shift |
| 2 | Fri Nov 6 | Laura |
| 3 | Sat Nov 7 | João |
| 4 | Sun Nov 8 | Priya |
| 5 | Mon Nov 9 | Tom |
| 6 | Tue Nov 10 | Ana |
| A | B | |
|---|---|---|
| 1 | Date | Shift |
| 2 | Fri Nov 6 | Laura |
| 3 | Sat Nov 7 | João |
| 4 | Sun Nov 8 | Priya |
| 5 | Mon Nov 9 | Tom |
| 6 | Tue Nov 10 | Ana |
Step by step
- Select the data, from row 2 down.
- Go to Home → Conditional formatting → New rule → Use a formula.
- Paste the formula:
=WEEKDAY($A2, 2)>5 - Click Format, pick a fill colour and press OK.
In Google Sheets: Format → Conditional formatting → Custom formula is.
Where it works
- Every version of Excel
- Google Sheets
- Excel for the web (free)
The same formula in other languages
- English Highlight weekends
- Español Resalta fines de semana
- Português Destaque os fins de semana