DATE

What it does

DATE(year, month, day)

DATE is the constructor of the date category. It takes three numbers — a year, a month and a day — and returns the date serial number that denotes the calendar day those three numbers name. Everything else in the category either consumes a serial (YEAR, MONTH, WEEKDAY, EOMONTH) or produces one (TODAY, EDATE, WORKDAY). DATE is where the mapping is defined.

The important thing to understand is that DATE is not a validator. It is a normalizing constructor: it does not reject 14 as a month or 35 as a day, it carries them. The documented rule is arithmetic, not calendrical. Given (y, m, d), Excel first resolves the year–month pair by ordinary integer division — month 14 of 2008 means month 2 of 2009, month −3 of 2008 means month 9 of 2007 — and then counts d days from the day before the first of that resolved month. So DATE(2008,1,35) is 4 February 2008 and DATE(2008,1,-15) is 16 December 2007, both of which Microsoft's page gives as worked examples.

Read that way, DATE(y, m, d) is the serial of

the first day of month m of year y, resolved by carrying month overflow into the year, then advanced by d − 1 days.

That single sentence explains all four documented rollover cases at once, and it is why DATE(2008,2,30) quietly becomes 1 March 2008 rather than an error.

The serial-number model

A date serial is a plain Number (see the value universe); the date-ness lives in the cell format, not in the value. Under the default 1900 date system, Microsoft documents that 1 January 1900 is serial 1 and 1 January 2008 is serial 39448. Those two anchors, together with the leap-year artefact below, pin the whole mapping:

The fractional part of a serial carries time of day: 0.5 is noon. DATE returns a whole number, so its result has no time component. See TIME for the other half of that model.

The 1900 leap-year artefact

1900 was not a leap year — it is divisible by 100 and not by 400 — but Excel's serial line contains a 29 February 1900 anyway. Microsoft documents both the fact and the reason: Lotus 1-2-3 made the assumption first, and Multiplan and Excel adopted it so that serial numbers would be interchangeable between the programs. Microsoft's article is explicit that the behaviour is retained deliberately and that only the year 1900 is affected; century years such as 2100 are handled correctly.

Two consequences bite in this category:

1. Every serial from 61 upwards is one greater than the true day count from 1 January 1900. This is invisible in day-difference arithmetic as long as both endpoints are on the same side of the discontinuity, and wrong by one when they straddle it.
2. Weekday alignment before 1 March 1900 is off by one. Microsoft's own article names this as the single residual problem: WEEKDAY "returns incorrect values for dates before March 1, 1900". See WEEKDAY.

Whether DATE(1900,2,29) returns serial 60 rather than rolling into 1 March, and what DATE(1900,2,30) does, are exactly the questions the artefact makes interesting. Neither has been checked against Excel for this Handbook.

The 1900 and 1904 date systems

A workbook carries a date-system flag. Microsoft documents the 1900 date system (the default) and the 1904 date system (the default in earlier versions of Excel for Mac), and states that the difference between the two is 1,462 days — "four years and one day (including one leap day)" — so that the same calendar day has a serial 1,462 higher in the 1900 system than in the 1904 system. Microsoft's worked example is 5 July 2011: serial 40729 in the 1900 system, 39267 in the 1904 system.

DATE is the function where this flag is consumed rather than merely observed: the same DATE(2011,7,5) call produces a different number in the two systems. Note that the 1904 system has no leap-year artefact, because 1904 genuinely was a leap year.

One honest inconsistency in the documentation, worth naming because implementers trip on it: Microsoft's date-systems page describes a serial as "the number of days elapsed since January 1, 1900", while the DATE, WEEKDAY and WEEKNUM pages state that 1 January 1900 is serial 1. Those two statements do not reconcile — "days elapsed since 1 January 1900" would make that day serial 0. The serial-1 anchor is the one consistent with the documented 39448 for 1 January 2008, so it is the one this Handbook uses.

Arguments

DATE(year, month, day) — all three required, no optional argument, no repeats.

year — Microsoft documents a three-branch rule, and it is the argument that surprises people:

The documentation also says Excel "interprets the year argument according to the date system your computer is using", which is the hook for 1904-system workbooks. The practical advice that follows from the table: always pass a four-digit year, because DATE(24,1,1) is 1 January 1924, not 2024.

month — an integer, documented as "positive or negative", nominally 1–12. Out-of-range values carry into the year as described above.

day — an integer, documented as "positive or negative", nominally 1–31. Out-of-range values carry into the month, and through the month into the year.

Non-integer arguments are the commonly misunderstood case that the documentation does not settle on this page. YEARFRAC's page states the truncation rule for its arguments explicitly; the DATE page does not, and this Handbook has not checked whether DATE(2008.9, 1.9, 1.9) truncates, rounds, or does something else.

Try it

Quirks

Result and edge cases

Returns a Number: a whole date serial. The cell will usually display it as a date, because Excel applies a date format to the result — that is host-side adaptation, not part of the value (see the call pipeline, stage 4).

Argument coercion follows the ordinary scalar rules of coercion and lifting: numeric text converts, logicals convert to 1 and 0, error inputs propagate. Nothing on this page overrides those.

Errors

As documented on the Microsoft page linked below:

• #NUM! when year is negative or 10000 or greater.

Beyond that single documented condition, the errors reachable from DATE are the ordinary ones that any numeric-argument function can produce: #VALUE! from an argument that cannot convert to a number, and any error value passed in as an argument propagating out. Microsoft's page does not enumerate a #NUM! for a resulting date outside 1 January 1900 – 31 December 9999 — for example DATE(1900,1,-5), which resolves below serial 0 — and this Handbook has not checked what Excel returns there.

How to implement

Notes for implementers

1. Do the month arithmetic before the day arithmetic, and do it in integers. Resolve (y, m) to a normalized year and 1–12 month with a floor-division carry (not truncating division — month = -3 must go backwards), then add day − 1 days. Getting the negative branch right is the whole trick.
2. The leap-year artefact is a coordinate shift, not a calendar rule. The cleanest implementation computes a proper proleptic Gregorian day number and then adds 1 for days on or after 1 March 1900. Trying to make a real calendar library believe in 29 February 1900 is the route to subtle bugs.
3. The date system is workbook state, not function state. A kernel that hard-codes the 1900 origin cannot serve a 1904 workbook. Model the origin as an input to the function, sourced from the execution context.
4. year in 0…1899 is add-1900, not a two-digit-window rule. It is not the sliding pivot convention used elsewhere in date parsing; 99 means 1999 and 5 means 1905.

Rust code

1 date_fn "DATE" (3, 3) [V "year", V "month", V "day"] |s, a, sv| {
    let system = system(s)?;
    let (y, m, d) = (num(s, a, 0)?, num(s, a, 1)?, num(s, a, 2)?);
    out(s, k::date(y, m, d, system))
}

/// DATE in the 1900 system: the year capped and read as Excel's signed 16-bit year, years below
/// 1900 offset by 1900, the month normalized into the year, and the day running on.
pub(crate) fn date(raw_year: f64, raw_month: f64, raw_day: f64, system: DateSystem) -> Result<f64, W> {
    if !raw_year.is_finite() || !raw_month.is_finite() || !raw_day.is_finite() {
        return Err(W::Num);
    }
    let original_year = date_integer_floor(raw_year).min(10_000.0);
    let year_bits = if (-2_147_483_648.0..2_147_483_648.0).contains(&original_year) {
        original_year as i64
    } else {
        0
    };
    let year = year_bits as i16 as i64;
    let year = year + if year < 1900 { 1900 } else { 0 };
    let month = date_integer_floor(raw_month);
    if !(-32_767.0..32_767.0).contains(&month) {
        return Err(W::Num);
    }
    let month = month as i64;
    let day = date_integer_floor(raw_day);
    let day = if (-32_768.0..=32_767.0).contains(&day) {
        day as i64
    } else {
        32_767
    };
    let month_index = year
        .checked_mul(12)
        .and_then(|v| v.checked_add(month - 1))
        .ok_or(W::Num)?;
    let normalized_year = month_index.div_euclid(12);
    let normalized_month = month_index.rem_euclid(12) + 1;
    if !(1900..=9999).contains(&normalized_year) {
        return Err(W::Num);
    }
    let serial = serial_unbounded_1900(normalized_year, normalized_month, day);
    if !(0..=MAX_SERIAL_1900).contains(&serial) {
        return Err(W::Num);
    }
    match system {
        DateSystem::System1900 => Ok(serial as f64),
        DateSystem::System1904 => {
            // A 1904 workbook has no 29 February 1900; a date before 1 January 1904 is #NUM!.
            let serial = serial as f64 - OFFSET_1904;
            if serial < 0.0 {
                Err(W::Num)
            } else {
                Ok(serial)
            }
        }
    }
}

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

• Inverses. YEAR, MONTH and DAY decompose a serial back into the three components DATE composes. DATE(YEAR(s), MONTH(s), DAY(s)) should return the whole-day part of s for any in-range serial; whether it does across the leap-year discontinuity is a natural metamorphic probe nobody has run here.
• Sibling constructor. TIME builds the fractional part the same way DATE builds the integer part, and with the same normalizing (rather than validating) philosophy.
• Text parser. DATEVALUE reaches the same serials from text instead of from three numbers, and is locale-dependent where DATE is not.
• Month arithmetic. EDATE and EOMONTH offset by whole months, which is not what DATE's month rollover does. DATE(2008,1,31) rewritten as DATE(2008,2,31) gives 2 March 2008, because DATE carries the excess days forward; EDATE is widely understood to clamp instead, though its Microsoft page does not state the clamping rule and this Handbook has not verified it. This is the most common confusion in the category.
• Confusable. DATEVALUE and DATE are frequently interchanged in advice; so are DATE and the TEXT function, which formats rather than constructs.

Named variants

No named variants are recorded yet.