Cellwise Try it free

Excel guide · 3 min read

Check if a date is a weekend in Excel

Quick answerUse =IF(WEEKDAY(A2,2)>5,"Weekend","Weekday"). WEEKDAY with 2 as the second argument numbers Monday as 1 through Sunday as 7, so anything above 5 is a Saturday or Sunday.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCDE
1Delivery dateCustomerFromTo
2Nov 6, 2026PatelNov 1, 2026Nov 30, 2026
3Nov 7, 2026Nguyen
4Nov 8, 2026Okoro
5Nov 9, 2026Silva
6Nov 13, 2026Berg
7Nov 14, 2026Haddad
8Nov 16, 2026Kowalski
9Mensah (date TBC)

Download this sheet as an Excel practice file (.xlsx)

Flag Saturdays and Sundays with WEEKDAY

WEEKDAY returns a number for the day of the week. Without a second argument it counts Sunday as 1 and Saturday as 7, which makes the weekend test awkward. Return type 2 puts the weekend at the end, 6 and 7, so one comparison does it.

November 7, 2026 is a Saturday, so the Nguyen delivery is flagged.

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

Result: Weekend

Weekend: WEEKDAY returns 6 for Saturday, which is greater than 5.

Watch out for blank date cells

The Mensah delivery has no date yet. Excel treats an empty cell as 0, and day 0 counts as a Saturday, so the plain formula labels a missing date as a weekend. That quietly inflates any weekend count.

Test for a blank first and return an empty string.

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

Result: "" (empty text, so the cell looks blank)

An empty result for the row with no date, instead of a false Weekend.

Friday and Saturday weekends

In several countries, including Saudi Arabia and Egypt, the weekend is Friday and Saturday. With the default return type, Friday is 6 and Saturday is 7, so test for either.

For other patterns (Sunday only, Thursday-Friday), test the specific day numbers the same way. In NETWORKDAYS.INTL and WORKDAY.INTL you pick the weekend with a code instead: 7 means Friday-Saturday, or use a seven-digit string from Monday to Sunday such as "0000110", where 1 marks a non-working day.

=IF(OR(WEEKDAY(A2)=6,WEEKDAY(A2)=7),"Weekend","Workday")

Result: Weekend

Weekend: November 6, 2026 is a Friday, so the Patel delivery falls on the weekend in this pattern.

Move a weekend date to Monday

For a due date or delivery date that must land on a workday, add 2 days to a Saturday and 1 to a Sunday. Weekdays stay as they are.

WORKDAY(A3-1,1) gives the same result in Excel: it finds the first workday from the day before, which is the date itself if it is already a weekday. Add a holiday range as the third argument to skip public holidays too.

=TEXT(A3+IF(WEEKDAY(A3,2)=6,2,IF(WEEKDAY(A3,2)=7,1,0)),"ddd mmm d")

Result: Mon Nov 9

Mon Nov 9: the Saturday delivery moves to the following Monday.

Count weekend days

To count weekend dates in a list, wrap the test in SUMPRODUCT. The -- turns TRUE and FALSE into 1 and 0. Here the empty cell would add a false Saturday, so the list stops at row 8.

To count weekend days in a whole period instead, take the total number of days and subtract the workdays: =E2-D2+1-NETWORKDAYS(D2,E2) returns 9 for November 2026. NETWORKDAYS counts Monday to Friday, both ends included.

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

Result: 3

3 deliveries fall on a Saturday or Sunday: Nguyen, Okoro and Haddad.

Highlight weekend rows

Conditional formatting can shade weekends without a helper column. The $ in $A2 keeps every cell in a row looking at the date column, so the whole row is shaded.

  1. Select A2:B9.
  2. Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
  3. Enter =AND($A2<>"",WEEKDAY($A2,2)>5) so blank dates stay unshaded.
  4. Click Format, choose a fill colour and click OK twice.

Common mistakes

Using WEEKDAY(A2)>5 without the 2

With the default numbering Friday is 6 and Saturday is 7, and Sunday is 1. Add 2 as the second argument.

Blank dates counted as Saturdays

Check A2="" first. An empty cell is day 0, which Excel treats as a Saturday.

Dates stored as text

Text that looks like a date may give #VALUE! or a wrong day. Convert the column to real dates first.

Forgetting public holidays

WEEKDAY only knows days of the week. Use NETWORKDAYS or WORKDAY with a holiday list for workdays.

FAQ

How do I check if a date is a weekend in Excel?

=WEEKDAY(A2,2)>5 returns TRUE for Saturday and Sunday. Wrap it in IF to show a label.

How do I check if a date is a weekday?

=WEEKDAY(A2,2)<6 returns TRUE for Monday to Friday.

How do I count weekends between two dates?

=end-start+1-NETWORKDAYS(start,end). It subtracts the Monday to Friday days from all the days in the period.

How do I show the day name next to a date?

=TEXT(A2,"dddd") shows the full name, such as Saturday, and "ddd" shows Sat.

Functions used

Related guides

Updated