WEEKDAY

What it does

WEEKDAY(serial_number, [return_type])

WEEKDAY returns which day of the week a date falls on, as a number. The mapping from serial to weekday is fixed by the serial line; what return_type selects is only the numbering — which day counts as 1, and whether the range starts at 0 or 1.

Underneath the ten documented conventions there is one function and two knobs: an origin day and a zero-or-one base. Everything in the table below is that single rotation.

Three features of that table are worth pointing at, because they are the source of most WEEKDAY bugs:

1. return_type 3 is the only zero-based option. Formulas that switch between 2 and 3 without adjusting are off by one everywhere.
2. 2 and 11 are duplicates, and 1 and 17 are duplicates. The 11–17 block is the later, regular family (start day = return_type − 10, counting Monday as 1); 1, 2 and 3 are the legacy options that predate it.
3. There is no return_type for ISO week semantics. WEEKDAY(s, 2) is the ISO-compatible day numbering (Monday = 1), but week numbering is a separate function — see ISOWEEKNUM.

The 1900 artefact, in Microsoft's own words

Weekday is where Excel's phantom 29 February 1900 becomes an observable wrong answer rather than a harmless coordinate shift. Because serial 60 occupies a slot that no real day occupies, every serial from 1 to 59 sits one position out of phase with the true weekday of the day it names. Microsoft's leap-year article states this plainly as the one residual problem of not fixing the bug: WEEKDAY "returns incorrect values for dates before March 1, 1900". Dates from 1 March 1900 onwards are unaffected.

This is not a defect this Handbook is alleging; it is documented by the vendor. What has not been checked here is exactly where the phase break falls in a running Excel — see below.

Arguments

WEEKDAY(serial_number, [return_type]) — one required argument and one optional.

serial_number — "a sequential number that represents the date of the day you are trying to find". Microsoft's page repeats the category advice to use DATE rather than text, since text dates may be interpreted differently.

return_type — optional; the numbering convention, per the table above. Omitted means 1 (Sunday = 1). Values outside the documented set are an error, not a silent default.

The commonly misunderstood position is return_type itself: readers assume the number names the first day of the week directly, which is true for 11–17 (offset by 10) and false for 1, 2 and 3.

Try it

Quirks

Result and edge cases

Returns a Number in 1…7, or 0…6 for return_type 3.

• Fractional serials are floored; time of day is irrelevant.
• Serials below 61 are in the region Microsoft's own article says returns incorrect values.
• Serial 60 — the phantom day itself. There is no true weekday for a day that did not exist, so whatever Excel returns here is a convention, not a fact.
• A 1904-system workbook has no phase problem at all, because 1904 was genuinely a leap year; the same calendar day also has a different serial, so a formula that hard-codes serials and a formula that uses DATE behave differently across the two systems.

Errors

As documented on the Microsoft page:

• #NUM! when serial_number is out of range for the current date-base value.
• #NUM! when return_type is outside the range in the table above.

#VALUE! from an argument that will not coerce to a number, and propagation of an error argument, are the ordinary behaviours from coercion and lifting, not WEEKDAY-specific rules.

How to implement

Notes for implementers

1. Implement one rotation, not ten branches. Compute a canonical weekday index from the serial (for example Monday = 0), then map through a small table of (origin, base) pairs keyed by return_type. Ten independent branches is ten chances to mis-key a row.
2. Validate return_type against the exact documented set — {1, 2, 3, 11, 12, 13, 14, 15, 16, 17}. It is not a range: 4 through 10 are invalid, and so is 18.
3. Derive the weekday from the serial, not from a calendar library. This is the function where the leap-year artefact must survive rather than be corrected; a real calendar consulted for "what weekday was 15 January 1900" gives the true answer, which is the answer Excel's own documentation says Excel does not give.
4. Beware MOD with negative operands if the canonical index is computed by modular arithmetic; the serial range is guarded, but intermediate offsets need not be.

Rust code

16 weekday_fn "WEEKDAY" (1, 2) [V "serial_number", V "return_type"] |s, a, sv| {
    let serial = date_arg(s, a, 0)?;
    let kind = return_type(s, a, false)?;
    out(s, k::weekday(serial, kind))
}

No implementation has been published with a Handbook test suite for this function.

Version differences

The recorded environments below distinguish a reading from a same-input comparison. Counts describe captured inputs in each environment; overlapping captures and comparisons are not totals of unique worksheet calls.

The record contains no same-input comparison between these environments. Reading different inputs on different builds does not establish either a version change or cross-build agreement.

Recorded environments and comparison details.

Alternatives

Relationships

• WEEKNUM — the week-of-year counterpart, with its own and differently organized return_type table (1, 2, 11–17, 21). The two tables look similar and mean different things; the return_type values are not interchangeable between the functions.
• ISOWEEKNUM — ISO 8601 week numbering, equivalent to WEEKNUM(s, 21) on the documented reading.
• NETWORKDAYS and WORKDAY — the functions that consume the same weekday notion implicitly, with Saturday and Sunday hard-wired as the weekend; their .INTL variants make the weekend definition explicit instead.
• DAY — day of month, the routine confusion.
• The TEXT function with a "ddd"/"dddd" format code returns the weekday's name, and is locale-dependent where WEEKDAY is not.

Named variants

No named variants are recorded yet.