Excel WEEKDAY Function: Find Which Day of the Week a Date Falls On
Returns a number saying which day of the week a date is.
WEEKDAY takes a date and tells you which day of the week it landed on, as a number. Its whole difficulty is in the second argument, which decides what those numbers mean — and the default is one almost nobody wants.
Left alone, WEEKDAY numbers the week Sunday to Saturday as 1 to 7. Most of the world starts its week on Monday, so a weekend test written against the default has to check for 1 and 7 rather than 6 and 7, which reads backwards. Passing 2 as the second argument switches to Monday-to-Sunday numbering, where 6 and 7 are Saturday and Sunday and everything reads the way you expect.
As with MONTH, the number is for logic and TEXT is for display. =TEXT(B2, "dddd") gives "Friday"; WEEKDAY gives 6 with return_type 2, which is what you actually test against.
Syntax
=WEEKDAY(serial_number, [return_type])Arguments
serial_numberRequired- The date to examine.
return_typeOptional- 1 or omitted numbers Sunday=1 to Saturday=7. 2 numbers Monday=1 to Sunday=7, which is usually what you want. 3 numbers Monday=0 to Sunday=6.
The example data
Headers in row 1, data in A2:D5. Column B holds real dates; C and D hold numbers.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order | Placed | Year | Month |
| 2 | SO-4417 | 2024-03-15 | 2024 | 3 |
| 3 | SO-4418 | 2024-06-30 | 2024 | 6 |
| 4 | SO-4419 | 2024-11-04 | 2024 | 11 |
| 5 | SO-4420 | 2025-01-28 | 2025 | 1 |
Worked examples
=WEEKDAY(B2, 2)Result: 5
15 March 2024 was a Friday, which is 5 when the week starts on Monday.
=WEEKDAY(B2)Result: 6
The same date with the default numbering. Sunday is 1, so Friday is 6 — the source of most WEEKDAY confusion.
=WEEKDAY(B3, 2) > 5Result: TRUE
The weekend test, and the reason to use return_type 2: with Monday first, anything above 5 is a weekend.
=TEXT(B2, "dddd")Result: Friday
The day name for display. TEXT handles the label; WEEKDAY handles the logic.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
Weekend test flags the wrong days
Why it happens: return_type was omitted, so Sunday is 1 and Saturday is 7 — a >5 test catches Friday and Saturday instead of Saturday and Sunday.
How to fix it: Pass 2 as the second argument and the numbering matches how you think about the week.
#VALUE!
Why it happens: The date is text, or return_type is not one of the accepted values.
How to fix it: return_type accepts 1, 2, 3 and 11 to 17. Anything else errors.
#NUM!
Why it happens: The date is negative or the return_type is out of range.
How to fix it: Check the source cell holds a real date rather than a calculation that went below zero.
Tips worth knowing
- Always pass 2 unless you specifically want Sunday-first numbering. It costs one character and removes an entire class of bug.
- =TEXT(date, "ddd") gives the short day name, "dddd" the full one.
- Shade weekends in conditional formatting with =WEEKDAY($B2, 2) > 5.
- For counting working days between dates, NETWORKDAYS already skips weekends — you do not need WEEKDAY for that.
Frequently asked questions
How do I check whether a date is a weekend?
=WEEKDAY(A2, 2) > 5 returns TRUE for Saturday and Sunday. The 2 is essential — without it Sunday numbers as 1 and the test catches the wrong days.
Why does WEEKDAY say Sunday is 1?
It inherits the American convention where the week starts on Sunday, and the default has never changed for backwards compatibility. Pass 2 as the second argument for Monday-first numbering.
How do I get the day name?
WEEKDAY only returns numbers. Use =TEXT(A2, "dddd") for the full name or "ddd" for the abbreviation. Keep WEEKDAY for tests and TEXT for anything a person will read.
Related functions
Guides that use it
Longer reads where this function does real work in a real sheet.