WEEKDAY in Excel: Find Weekends and Day Names

The Excel WEEKDAY function tells you which day of the week a date falls on, as a number from 1 to 7. That small number lets you flag weekend deliveries, push renewals to Monday, group dates by week and highlight weekends in a schedule. This guide covers the syntax, the return types and the formulas built on top of it.

How does the Excel WEEKDAY function work?#

WEEKDAY takes a date and returns a number for its day of the week:

=WEEKDAY(serial_number, [return_type])

• serial_number is the date: a cell such as B2, or a DATE formula such as DATE(2025,3,15).
• return_type is optional. It sets which day counts as 1. Leave it out and Sunday is 1, Monday is 2, and so on to Saturday, which is 7.

Excel stores dates as serial numbers, so WEEKDAY works on any real date. If the date is text, you get #VALUE!. For how dates are stored, see DATE, YEAR, MONTH and DAY in Excel.

Which return type should you use?#

Return type 2 is the most useful for work, because it starts the week on Monday:

Here are some renewal dates with both numberings:

Don't mix up WEEKDAY and DAY. =DAY(B2) returns the day of the month (15 for Mar 15). =WEEKDAY(B2) returns the day of the week.

How to check if a date is a weekend#

With return type 2, Saturday is 6 and Sunday is 7, so weekends are the only days above 5:

=WEEKDAY(B2,2)>5

This returns TRUE for Bluefin, Orbit and Summit, and FALSE for the rest. For a label, put it inside IF:

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

With the default numbering you would need =OR(WEEKDAY(B2)=1,WEEKDAY(B2)=7), which is longer and easier to get wrong. That's why most people use return type 2 for weekend checks.

To count weekend dates in a list, use =SUMPRODUCT(--(WEEKDAY(B2:B7,2)>5)). For the six renewals above, it gives 3.

How to show the day name instead of a number#

WEEKDAY only returns numbers. For a name, use TEXT with a day format code:

TEXT returns text, so you can't do date maths with the result. The day names come out in the language of your Excel settings, so a French copy of Excel shows "samedi".

If the file is shared across countries and you need the same names everywhere, build them yourself with CHOOSE:

=CHOOSE(WEEKDAY(B2),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")

WEEKDAY gives a number from 1 to 7, and CHOOSE picks the matching item from the list, whatever language Excel uses.

To keep the real date but display the day name, use a custom number format instead: press Ctrl+1 (Windows) or ⌘1 (Mac), choose Custom, and type dddd. The cell still holds the date.

How to find the start of the week or the next Monday#

WEEKDAY tells you how far a date is from the start of its week, so simple maths moves to the day you want:

The first formula is handy for weekly reports: every date from Monday to Sunday maps to the same Monday, so you can group or total by week. The results are dates, so format the cells as dates. If you need to skip holidays as well as weekends, use WORKDAY instead; see NETWORKDAYS and WORKDAY.

How to highlight weekends with conditional formatting#

Use WEEKDAY in a formula rule. Select the dates, for example A2:A31, then go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter:

=WEEKDAY($A2,2)>5

Pick a fill colour and click OK. The $ before A keeps the rule on column A, so you can also apply it to whole rows. See conditional formatting basics for more rules.

What are the common WEEKDAY mistakes?#

The empty-cell case surprises people. Excel treats an empty cell as 0, and day 0 counts as a Saturday, so blank rows can show up as weekends.

WEEKDAY pairs well with the other date functions. Find a renewal with EDATE, then check its day: =WEEKDAY(EDATE(B2,12),2)>5. See EDATE and EOMONTH, or count the days between two dates.

Practise it#

Try the weekend exercise on this page, then practise EDATE, EOMONTH and WEEKDAY in a live spreadsheet. You'll find renewal dates, show day names with TEXT and flag the renewals that land on a weekend.