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

Q: How do I highlight weekends with conditional formatting?

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

Q: How do I count the weekend dates in a list?

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

Q: How do I apply a weekend pay rate?

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

Formula (Excel and Google Sheets)

=IF(WEEKDAY(A2,2)>5,"Weekend","Weekday")

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

Result on the example: one result per row in B2, filled down to B7
Works in: Every current version
Google Sheets: Same formula

See it work

The formula is already in B2, filled down to B7. Change any input and the result updates, or edit the formula to experiment.

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.

=IF(WEEKDAY(A2,2)>5,"Weekend","Weekday")

Result"Weekday"

A25/1/2026
The value in A2.
WEEKDAY(A2,2)5
WEEKDAY: Day of the week as a number; Sunday = 1 by default.
- serial_numberA25/1/2026
- return_type2
WEEKDAY(A2,2)>5FALSE
The > operator tests greater than.
IF(WEEKDAY(A2,2)>5,"Weekend","Weekday")"Weekday"
IF: Returns one value if a test is true, another if false.
- logical_testWEEKDAY(A2,2)>5FALSE
- value_if_true"Weekend"not used
- value_if_false"Weekday"

In Google Sheets

The same formula works in Google Sheets: =IF(WEEKDAY(A2,2)>5,"Weekend","Weekday"). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=WEEKDAY(A2,2)>5	FALSE, TRUE, TRUE…Different result	TRUE or FALSE, ready for conditional formatting, FILTER or COUNTIF. Returns TRUE/FALSE.
=IF(OR(WEEKDAY(A2)=1,WEEKDAY(A2)=7),"Weekend","Weekday")	Weekday, Weekend, Weekend…	Spells out Sunday (1) and Saturday (7) with the default numbering.
=IF(NETWORKDAYS(A2,A2)=0,"Weekend","Weekday")	Weekday, Weekend, Weekend…	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.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Check if a date falls on a weekend

In B2, show "Weekend" if the shift date is a Saturday or Sunday and "Weekday" otherwise. Fill down to B7.

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.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.