# How to check if a date falls on a weekend in Excel

Use `=IF(WEEKDAY(A2,2)>5,"Weekend","Weekday")`. With type 2, Saturday is 6 and Sunday is 7.

**Excel:** `=IF(WEEKDAY(A2,2)>5,"Weekend","Weekday")` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Shift date | Type |
| 2026-05-01 |  |
| 2026-05-02 |  |
| 2026-05-03 |  |
| 2026-05-04 |  |
| 2026-05-09 |  |
| 2026-05-13 |  |

Formula in B2, filled to B7: `=IF(WEEKDAY(A2,2)>5,"Weekend","Weekday")`

Result: Weekday; Weekend; Weekend; Weekday; Weekend; Weekday

## How it works

With 2 as the second argument, WEEKDAY numbers Monday as 1 through Sunday as 7, so Saturday and Sunday are the only days above 5.

## Other ways

- `=WEEKDAY(A2,2)>5`: TRUE or FALSE, ready for conditional formatting, FILTER or COUNTIF. (Returns TRUE/FALSE.)
- `=IF(OR(WEEKDAY(A2)=1,WEEKDAY(A2)=7),"Weekend","Weekday")`: Spells out Sunday (1) and Saturday (7) with the default numbering.
- `=IF(NETWORKDAYS(A2,A2)=0,"Weekend","Weekday")`: NETWORKDAYS of a single day is 0 on a weekend. Add a holiday list as a third argument to treat holidays as days off too.

## Common mistakes

- Without the 2, WEEKDAY starts at Sunday = 1, and `>5` flags Friday and Saturday instead.
- Holidays are not weekends. To treat both as days off, use `NETWORKDAYS(A2,A2,holidays)=0`.

## Questions

**How do I highlight weekends with conditional formatting?**

Use the rule `=WEEKDAY($A2,2)>5` on the date column.

**How do I count the weekend dates in a list?**

`=SUMPRODUCT(--(WEEKDAY(A2:A7,2)>5))`.

**How do I apply a weekend pay rate?**

`=IF(WEEKDAY(A2,2)>5,C2*1.5,C2)`, with the base rate in C2.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/check-if-a-date-is-a-weekend