How to Find the Day of the Week with WEEKDAY in Excel

Turn dates into weekday numbers, flag weekend bookings with return type 2, and total guests on weekends.

You will learn to

• Get a weekday number from a date
• Use return type 2 to test for weekends
• Total values that fall on weekends

How it works

WEEKDAY(date) returns a number for the day of the week. With the default return type 1, Sunday is 1 and Saturday is 7. With return type 2, Monday is 1 and Sunday is 7 — which makes weekends simply WEEKDAY(date,2)>5.

WEEKDAY works on whole ranges inside SUMPRODUCT, so =SUMPRODUCT((WEEKDAY(B2:B7,2)>5)*C2:C7) totals any column for weekend dates only, with no helper column.

=WEEKDAY(B2,2)