Excel WEEKDAY Function

WEEKDAY returns a number that tells you which day of the week a given date falls on. By default, 1 means Sunday and 7 means Saturday. That default trips up a lot of business schedules, where the week starts Monday, so the second argument matters more than most people assume.

Available in: Excel 365, Excel 2021, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007, and earlier. The core function works in every version. Return_type codes 11 through 17 require Excel 2010 or later, and the dedicated ISOWEEKDAY function requires Excel 2013 or later.

Syntax

=WEEKDAY(serial_number, [return_type])

Parameter	Required	Description
serial_number	Yes	The date you're checking. Can be a cell reference, a date typed in quotes, or the output of another formula like TODAY().
return_type	No	A code that sets which day is numbered 1 and where the week starts. Leave it out and Sunday = 1, Saturday = 7.

Basic Example

You're building a delivery log and need to flag which day of the week each shipment went out.

=WEEKDAY(D2, 1)

// D2 = the delivery date
// 1  = Sunday counts as 1, Saturday counts as 7 (the default)

If D2 contains January 1, 2000, this returns 7. That date was a Saturday, and return_type 1 assigns Saturday the number 7. Change the date and the result shifts automatically, no manual lookup required.

How WEEKDAY Works

return_type controls where the week starts

The second argument decides which day gets numbered 1 and, in some cases, whether Excel counts up or down through the week.

return_type	1 =	7 =
1 or omitted	Sunday	Saturday
2	Monday	Sunday
3	Monday (numbered 0)	Sunday (numbered 6)
11	Monday	Sunday
12	Tuesday	Monday
13	Wednesday	Tuesday
14	Thursday	Wednesday
15	Friday	Thursday
16	Saturday	Friday
17	Sunday	Saturday

Codes 11 through 17 only exist in Excel 2010 and later. If you're maintaining a workbook that might open in Excel 2007, stick to 1, 2, or 3.

WEEKDAY still returns a number for a blank cell

Point WEEKDAY at an empty cell and it doesn't throw an error. Excel treats a blank as serial number 0, which maps to a fictional date before January 1, 1900, and WEEKDAY(0,1) returns 7. This means a formula referencing an unfilled date row will happily report "Saturday" instead of flagging that the data is missing.

Subtracting from a blank cell can throw #NUM!

This is where the blank-cell behavior actually breaks. A formula like =WEEKDAY(B10-1) works fine on a real date, but if B10 is empty, Excel calculates 0 - 1 = -1. Negative serial numbers aren't valid dates, so the formula returns #NUM! instead of the silent 7 you'd get from referencing the blank cell directly.

ISO 8601 weekday numbers use return_type 2 or the ISOWEEKDAY function

If you need Monday numbered 1 and Sunday numbered 7, the ISO 8601 standard used in most of Europe and in project scheduling tools, use =WEEKDAY(date, 2). The dedicated ISOWEEKDAY function does the same thing without a second argument, and it's available in Excel 2013 and later. Both return identical results; ISOWEEKDAY just reads more clearly if a colleague opens the formula later.

Common Use Cases

Flag weekend rows in a schedule

You want a helper column that marks Saturday and Sunday shipments for follow-up.

=IF(WEEKDAY(A2, 2) > 5, "Weekend", "Weekday")   // return_type 2 makes Saturday = 6 and Sunday = 7

Get the Monday of the week for any date

Payroll runs weekly, and you need the Monday that starts each employee's pay period.

=A2 - WEEKDAY(A2, 3)

// A2       = any date in the target week
// return_type 3 numbers Monday as 0, so subtracting it lands on Monday

Count how many Fridays fall in a date range

You're auditing a project timeline and need to know how many Fridays occurred between the start and end dates. COUNTIF and COUNTIFS can't do this directly. Both need a range of actual weekday values to test against, and there's no way to feed WEEKDAY's array output into their criteria_range argument. SUMPRODUCT can, because it evaluates arrays natively:

=SUMPRODUCT(--(WEEKDAY(project_dates, 2) = 5))   // 5 = Friday under return_type 2

Find the next occurrence of a specific weekday

You need the next Friday delivery slot from today's date.

=A2 + MOD(6 - WEEKDAY(A2, 2), 7)

// A2   = today's date or the last delivery date
// 6    = Friday's position under return_type 2 (Monday=1...Sunday=7, so Friday=5, target offset uses 6)
// MOD wraps the result so it always lands on the next Friday, even if today is already Friday

Handling Errors

WEEKDAY throws two catchable errors. #VALUE! shows up when serial_number isn't something Excel can read as a date, such as a text string like "July 32" or a reference to a cell holding an error. #NUM! shows up when the calculated serial number is invalid, most often when a formula subtracts from a blank cell and produces a negative number.

Common causes of #VALUE!:

• serial_number is text Excel can't parse as a real date
• The cell referenced contains a different error, which WEEKDAY passes through
• A date was typed with an invalid day, like "February 30"

Common causes of #NUM!:

• The formula subtracts from a blank cell, producing a negative serial number
• serial_number resolves to a date before January 1, 1900

=IFERROR(WEEKDAY(A2, 2), "Check the date")

Notes & Gotchas

How do I use the WEEKDAY formula in Excel?

Point WEEKDAY at a date and, optionally, a return_type code that sets which day is numbered 1. =WEEKDAY(A2, 2) returns a number from 1 (Monday) through 7 (Sunday) for the date in A2. Most scheduling formulas pair this with IF, SUMPRODUCT, or conditional formatting rules.

How do I convert a date to a day name like "Monday" in Excel?

Use TEXT instead of WEEKDAY: =TEXT(A2, "dddd") returns the full day name directly, and =TEXT(A2, "ddd") returns the three-letter abbreviation. WEEKDAY isn't needed for this at all, since TEXT reads the date's weekday internally without any help.

What is the difference between WEEKDAY and TEXT for finding the day of the week?

WEEKDAY returns a number; TEXT returns a text label. If a formula downstream needs to compare, sort, or do math with the day of the week, use WEEKDAY. If you just need to display "Tuesday" in a cell or a report, use TEXT and skip WEEKDAY entirely.

How do I use WEEKDAY to identify or highlight weekends?

Wrap WEEKDAY in an IF test: =IF(WEEKDAY(A2, 2) > 5, "Weekend", "Weekday"). For conditional formatting, apply the rule =WEEKDAY($A2, 2) > 5 to a range and Excel highlights every Saturday and Sunday automatically as dates change.

How do I calculate workdays while excluding weekends using WEEKDAY?

For counting or shifting by workdays, use dedicated workday functions instead. Those functions already exclude weekends and optional holiday lists without any WEEKDAY logic. WEEKDAY is still useful for flagging individual weekend dates in a helper column, but it isn't built to calculate a range of workdays on its own.

Why does WEEKDAY return #NUM!?

The most common cause is a formula that subtracts a number from a blank cell, which produces a negative serial number. A negative number isn't a valid date, so WEEKDAY can't process it. Wrap the reference in an IF check, like =IF(B10="", "", WEEKDAY(B10-1)), to catch blanks before the subtraction happens.

What happens if the date cell is blank?

Referencing a blank cell directly doesn't throw an error. Excel treats the blank as serial number 0 and WEEKDAY(0,1) returns 7, silently reporting Saturday even though there's no real date there. This is different from the #NUM! error you get when a formula does math on that blank cell first.

Related Functions

Function	Use this when...
TEXT	You just need the day name displayed, like "Monday," without a numeric weekday value.