WEEKDAY in Excel
WEEKDAY(serial_number, [return_type]) returns the day of the week of a date as a number. By default 1 is Sunday and 7 is Saturday. With return type 2, 1 is Monday and 7 is Sunday, which makes a weekend test a simple comparison: greater than 5.
Syntax
WEEKDAY(serial_number, [return_type])| Part | What it is |
|---|---|
serial_number | The date, as a cell reference or a formula that returns a date. |
return_type | 1 or omitted: Sunday = 1 to Saturday = 7. 2: Monday = 1 to Sunday = 7. 3: Monday = 0 to Sunday = 6. |
Examples
=WEEKDAY(A2)Result: 6 when A2 is a Friday
Default numbering starts on Sunday.
=WEEKDAY(A2, 2)Result: 5 when A2 is a Friday
Monday is 1, so Saturday and Sunday are 6 and 7.
=IF(WEEKDAY(A2, 2)>5, "Weekend", "Weekday")Result: Weekend for a Saturday or Sunday date
=TEXT(A2, "dddd")Result: Friday
For the name of the day, TEXT is the function. WEEKDAY gives the number.
Common mistakes
- Testing for 6 and 7 with the default return type marks Friday and Saturday as the weekend. Use return type 2 with that test.
- #VALUE! means the date is stored as text. Convert it with DATEVALUE or re-enter it.
- Formatting the WEEKDAY result as a date shows a day in January 1900. Keep the cell format General.
- Expecting a day name: WEEKDAY returns a number. Use TEXT with dddd or ddd for the name.
Practice WEEKDAY
These drills check your answer on live data, free, no account needed.
- Weekend or weekdayLabel each date as Weekend (Saturday or Sunday) or Weekday. Fill B2 to B4.
- Ship-by datesEach order must ship a number of working days after the order date. Weekends do not count. Put the ship-by date in D2 and D3 with WORKDAY.
Questions
How do I check whether a date is a weekend in Excel?
=WEEKDAY(A2, 2)>5 is TRUE for Saturday and Sunday. Wrap it in IF to return a label.
How do I get the name of the day instead of a number?
=TEXT(A2, "dddd") returns the full name and =TEXT(A2, "ddd") the short one.
How do I count working days instead?
Use NETWORKDAYS(start, end), which leaves out Saturdays, Sundays and an optional list of holidays.
Related
- NETWORKDAYS and WORKDAYHow NETWORKDAYS counts the working days between two dates and WORKDAY adds working days, skipping weekends and holidays, and .INTL for other weekends.
- DATE and DATEDIFHow the Excel DATE function builds a date from year, month and day, and DATEDIF counts complete years, months or days between two dates, plus an age formula.
- TEXTHow the Excel TEXT function turns a number or date into text in a format you choose, with examples for dates, thousand separators, percentages and padding.
- TODAY and NOWHow the Excel TODAY and NOW functions insert the current date or date and time, with formulas for days until a deadline, age, overdue flags and fixed dates.
Previous: TEXTBEFORE and TEXTAFTERNext: CEILING, FLOOR and MROUND