DATEDIF

What it does

DATEDIF(start_date, end_date, unit)

DATEDIF measures the interval between two dates in a calendar unit named by a text code. It is the only function in this category whose unit is an argument rather than baked into the name, and it is the only one Microsoft explicitly describes as retained for compatibility: "Excel provides the DATEDIF function in order to support older workbooks from Lotus 1-2-3."

Its status is genuinely unusual and worth stating plainly, because readers meet the function through folklore before they meet it through documentation:

• It is documented. Microsoft publishes a support page for it, cited below, with a syntax line, a unit table, a caveat and an error condition.
• It is widely reported to be absent from Excel's in-product function surface — formula autocomplete, the function wizard, argument tooltips — which is why readers meet it as "hidden" or "undocumented". This Handbook has not verified the in-product behaviour in any Excel build, so it is recorded here as a widely-reported property and not as a finding. What can be said from the record is narrower and sufficient: the function is documented but unadvertised.
• Microsoft's page discourages one of its own unit codes: "We don't recommend using the 'MD' argument, as there are known limitations with it."

The six documented units, verbatim from that page:

Two different kinds of thing are in that table, and the mixture is the source of the surprise:

• "Y", "M", "D" are interval counts — how much whole time has elapsed. "Y" is the "complete years" that make it the standard age formula, and it is not YEAR(end) − YEAR(start).
• "MD", "YM", "YD" are component remainders — what is left over after the larger units are removed. They exist so that Y/YM/MD can be composed into "3 years, 2 months and 17 days".

The remainder units are where the function's reputation comes from. "MD" compares two day-of-month numbers with the months and years discarded; when the end day-of-month is smaller than the start day-of-month, the subtraction has to borrow from a month whose length depends on which month you decide to borrow from — and there is no canonical answer. Microsoft's own page reports the consequence rather than the mechanism: "MD" can produce negative values, zero, or inaccurate results. This Handbook records that as documented vendor guidance, not as a measured characterization; nobody has determined here which inputs misbehave or why.

Arguments

DATEDIF(start_date, end_date, unit) — all three required. Note the argument order: start first, unlike DAYS, which takes the end date first.

start_date — the beginning of the period, as a date serial. Microsoft's page treats it as a serial like the rest of the category.

end_date — the end of the period. Must not be earlier than start_date; see Errors.

unit — text, one of the six codes above. The codes are conventionally written uppercase; the documentation gives them uppercase and does not state whether matching is case-insensitive, which is a small but real open question for an implementer.

The misunderstood argument is unit, in two ways. Readers reach for "M" expecting a month difference and get complete months elapsed; and readers reach for "YD" expecting "days into the year" and get something defined by discarding the years from both dates, which is not the same thing when the two dates sit either side of 29 February.

Try it

Quirks

Result and edge cases

Returns a Number: an integer count in the requested unit.

• "Y" is complete years, and that is why it works for ages. DATEDIF(birth, today, "Y") is correct on the day before a birthday and correct on the birthday, where YEAR(today) − YEAR(birth) is wrong on one of them.
• "D" is a plain day count, and Microsoft's page says so explicitly, adding: "If you want to find the number of days between two dates, simply subtract the later date from the earlier date." That is an unusually candid piece of documentation — the vendor telling you the function is unnecessary for its simplest unit. (The sentence as published says to subtract "the later date from the earlier date", which would give a negative count; the intent is plainly the other way round. Quoted here as written, per the Handbook's practice of not silently repairing sources.)
• "MD" is flagged by Microsoft as unreliable and should be treated as such.
• Leap-day arguments are where "YM" and "YD" are most likely to surprise, because discarding the year from 29 February leaves a date that does not exist in most years.
• Equal dates give 0 in every unit.
• Time components — the page does not state whether fractional serials are truncated before the comparison. Unverified here.

Errors

As documented on the Microsoft page:

• #NUM! "if the start_date is greater than the end_date". This is a strict, documented asymmetry: DATEDIF refuses negative intervals where plain subtraction and DAYS simply go negative. Any wrapper that feeds unsorted date pairs to DATEDIF will surface #NUM! for half of them.

#VALUE! for an unrecognized unit code is the behaviour readers expect and is not stated on that page; it is not asserted here. Ordinary coercion failures and error propagation follow the engine rules in coercion and lifting.

How to implement

Notes for implementers

1. Implement the three interval units and the three remainder units as separate ideas. The remainder units are not "the interval units modulo something"; they are defined by discarding components of the operands, before any subtraction.
2. "MD" needs a documented borrowing rule, and the documentation does not supply one. Whatever an implementation does, it should say so, and it should not claim to match Excel until someone has looked. This is the single largest unknown in the function.
3. Enforce the start > end error before anything else. It is the one documented error and it changes the shape of the whole contract.
4. Decide and record the unit matching rule — case sensitivity, surrounding whitespace, whether a non-text argument coerces. None of it is documented.
5. Do not implement "Y" as a year subtraction with a correction. Compute it as "the largest n such that start advanced by n years is not after end", which is correct by construction and makes the leap-day cases fall out rather than needing patches.

Rust code

11 datedif_fn "DATEDIF" (3, 3) [V "start_date", V "end_date", V "unit"] |s, a, sv| {
    if let Some(code) = a.iter().find_map(CalcValue::as_error) {
        return fail(code);
    }
    let start = date_arg(s, a, 0)?;
    let end = date_arg(s, a, 1)?;
    let unit = text_arg(s, a, 2)?;
    let unit = k::datedif_unit(&unit.to_string_lossy()).map_err(Exit::Error)?;
    out(s, k::datedif(start, end, unit))
}

pub(crate) fn datedif_unit(unit: &str) -> Result<DateDiffUnit, W> {
    match unit.trim().to_ascii_uppercase().as_str() {
        "Y" => Ok(DateDiffUnit::Y),
        "M" => Ok(DateDiffUnit::M),
        "D" => Ok(DateDiffUnit::D),
        "YM" => Ok(DateDiffUnit::Ym),
        "YD" => Ok(DateDiffUnit::Yd),
        "MD" => Ok(DateDiffUnit::Md),
        _ => Err(W::Num),
    }
}

fn last_day_of_previous_month(year: i64, month: i64) -> i64 {
    let (y, m) = if month == 1 {
        (year - 1, 12)
    } else {
        (year, month - 1)
    };
    days_in_month(y, m)
}

pub(crate) fn datedif(start_serial: f64, end_serial: f64, unit: DateDiffUnit) -> Result<f64, W> {
    let start = start_serial.trunc();
    let end = end_serial.trunc();
    if !start.is_finite() || !end.is_finite() || start < 0.0 || end < 0.0 || end < start {
        return Err(W::Num);
    }
    let (sy, sm, sd) = ymd_of(start)?;
    let (ey, em, ed) = ymd_of(end)?;
    let serial_of = |y: i64, m: i64, d: i64| {
        excel_serial_from_ymd(DateSystem::System1900, y, m, d).ok_or(W::Num)
    };
    match unit {
        DateDiffUnit::D => Ok(end - start),
        DateDiffUnit::Y => {
            let mut years = ey - sy;
            if (em, ed) < (sm, sd) {
                years -= 1;
            }
            Ok(years as f64)
        }
        DateDiffUnit::M => {
            let mut months = (ey - sy) * 12 + (em - sm);
            if ed < sd {
                months -= 1;
            }
            Ok(months as f64)
        }
        DateDiffUnit::Ym => {
            let mut months = (em - sm).rem_euclid(12);
            if ed < sd {
                months = (months - 1).rem_euclid(12);
            }
            Ok(months as f64)
        }
        DateDiffUnit::Yd => {
            let candidate = serial_of(ey, sm, sd.min(days_in_month(ey, sm)))?;
            if candidate <= end {
                Ok(end - candidate)
            } else {
                let previous = serial_of(ey - 1, sm, sd.min(days_in_month(ey - 1, sm)))?;
                Ok(end - previous)
            }
        }
        DateDiffUnit::Md => Ok(if ed >= sd {
            ed - sd
        } else {
            ed + last_day_of_previous_month(ey, em) - sd
        } as f64),
    }
}

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

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• DAYS and the − operator — the direct competitors for "D", both of which tolerate a negative interval where DATEDIF errors, and one of which Microsoft's own page recommends over DATEDIF.
• YEARFRAC — the fractional interval in years, on a selectable day-count basis. YEARFRAC answers "how much of a year", DATEDIF("Y") answers "how many whole years"; they are frequently substituted for one another and are not interchangeable.
• EDATE — the inverse operation for months: DATEDIF(a, EDATE(a, n), "M") should be n for any n ≥ 0, which is the obvious metamorphic relation for this function and, as far as this Handbook records, has never been run.
• DAYS360 — also an interval, but on the accounting 30/360 convention.

Named variants

No named variants are recorded yet.