WEEKNUM

What it does

WEEKNUM(serial_number, [return_type])

WEEKNUM returns the ordinal number of the week within the year that a date falls in. The function carries two genuinely different definitions, selected by return_type, and confusing them is the source of nearly every disagreement between spreadsheets about "which week is it".

Microsoft states the two systems as follows:

• System 1 — "The week containing January 1 is the first week of the year, and is numbered week 1." Under this system week 1 can be a stub of a single day, and a year can contain 54 partial weeks' worth of numbering in the worst case.
• System 2 — "The week containing the first Thursday of the year is the first week of the year, and is numbered as week 1. This system is the methodology specified in ISO 8601, which is commonly known as the European week numbering system."

The documented return_type table:

Note the shape: nine of the ten options are System 1 and differ only in which weekday starts the week; the tenth, 21, is the ISO system and is the only one where the week-1 rule itself changes. return_type 2 and 11 are duplicates, as are 1 and 17 — the same redundancy the WEEKDAY table has, for the same historical reason.

Under System 1, the algorithm is: week n runs from the n-th week-start day on or before the first week-start day following 1 January, with the partial week containing 1 January counted as week 1. Under System 2 the algorithm is the ISO 8601 one, in which weeks always have seven days, always begin on Monday, and the last days of December can belong to week 1 of the following year — which is why an ISO week number alone is ambiguous without its ISO year.

Arguments

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

serial_number — "a date within the week". Microsoft's page repeats the serial-number model: "Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1." Supply dates from DATE or from another formula rather than as text.

return_type — optional; the week-start day and system, per the table. Omitted means 1 (weeks begin Sunday, System 1).

The misunderstood argument is again return_type, in three distinct ways: readers assume its values match WEEKDAY's (they overlap but do not mean the same thing), assume 21 is "just Monday" (it changes the entire week-1 rule), and assume there is a System 2 variant for other start days (there is not — ISO weeks are Monday-based by definition).

Try it

Quirks

Result and edge cases

Returns a Number: a week ordinal.

• Early January. This is where the two systems visibly disagree. A 1 January falling on a Friday is week 1 under System 1 and week 53 of the previous year under ISO.
• Late December. Under System 1 a year can reach week 53 or 54 depending on the start day; under ISO the last days of December may be week 1 of the next year.
• The result carries no year. WEEKNUM returns a number, not a (year, week) pair, so ISO week numbers obtained from it must be paired with an ISO year computed separately — Excel provides no ISO-year function.
• Serials in 1900. Week numbering near the start of the serial line inherits the phase problem described on WEEKDAY, because week boundaries are weekday boundaries.

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 specified in the table.

Excel does not always follow the second of these: for an undocumented return_type it usually gives #NUM!, sometimes a number, and the same call can answer differently (FN-WEEKNUM-001).

Ordinary coercion failures give #VALUE!, and an error argument propagates; those are engine behaviours (see coercion and lifting), not WEEKNUM rules.

How to implement

Notes for implementers

1. Implement System 1 and System 2 as separate algorithms. They are not the same computation with a different start day; System 2's week 1 is defined by the first Thursday, System 1's by 1 January. A single parameterized routine that tries to cover both usually gets the year boundary wrong in one of them.
2. Validate return_type against the exact set {1, 2, 11, 12, 13, 14, 15, 16, 17, 21}. It is not a range; 3 is valid for WEEKDAY and invalid here, which is a genuinely easy mistake given how the two tables are usually read side by side.
3. Do not compute ISO weeks by rounding day-of-year. The standard ⌊(doy + 6 − isoweekday)/7⌋ style formula needs its year-boundary corrections; get them from the ISO definition rather than by patching failures.
4. Reuse the weekday kernel shared with WEEKDAY, so that the 1900 phase behaviour is the same in both functions by construction rather than by coincidence.

Rust code

17 weeknum_fn "WEEKNUM" (1, 2) [V "serial_number", V "return_type"] |s, a, sv| {
    let serial = strict_date_arg(s, a, 0)?;
    let kind = return_type(s, a, true)?;
    out(s, k::weeknum(serial, kind))
}

pub(crate) fn weeknum(serial: f64, return_type: Option<f64>) -> Result<f64, W> {
    let serial = whole_serial(serial)?;
    let return_type = whole(date_integer_floor(return_type.unwrap_or(1.0)))?;
    if return_type == 21 {
        return iso_weeknum_serial(serial);
    }
    if remap_weekday(1, return_type).is_none() || return_type == 3 {
        return Err(W::Num);
    }
    let (year, _, _) = ymd_1900(serial);
    let jan1 = serial_unbounded_1900(year, 1, 1);
    let jan1_weekday = weekday(jan1 as f64, Some(return_type as f64))? as i64;
    let week1_start = jan1 - (jan1_weekday - 1);
    Ok(((serial - week1_start).div_euclid(7) + 1) as f64)
}

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.

Recorded variants

Alternatives

Relationships

• ISOWEEKNUM — the dedicated ISO 8601 function, which on the documented reading computes the same thing as WEEKNUM(s, 21). Having two spellings of one definition is itself a fact about the surface, and whether they agree on every input is a natural equivalence test that nobody has run for this Handbook.
• WEEKDAY — the day-of-week function whose return_type table looks similar and is not interchangeable.
• DATE — the recommended way to construct the argument.
• Confusable. WEEKNUM(s,1) and WEEKNUM(s,21) differ for a large fraction of January and December dates; reports that "Excel gives the wrong week number" are almost always this.

Named variants

No named variants are recorded yet.