Formuladrill

Dates Updated November 2026

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])
PartWhat it is
serial_numberThe date, as a cell reference or a formula that returns a date.
return_type1 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