DAYS

What it does

DAYS(end_date, start_date)

DAYS returns the signed number of days from start_date to end_date. Microsoft's page states the rule directly: when both arguments are numbers, DAYS computes end − start. So it is subtraction with a name — and with a documented coercion policy that plain subtraction does not have.

Two things make it worth a page of its own rather than a footnote to the − operator:

1. The argument order is end-first. DAYS(end_date, start_date). Every other two-date function in the category — DATEDIF, DAYS360, NETWORKDAYS, YEARFRAC — takes start first. DAYS is the exception, and getting it wrong silently returns the negation rather than an error.
2. Text arguments are routed through DATEVALUE, not through ordinary numeric coercion. Microsoft documents that "if either one of the date arguments is text, that argument is treated as DATEVALUE(date_text)". This is a per-function coercion policy, not the engine-wide text-to-number rule described in coercion and lifting — and it drags DAYS into locale dependence that the numeric path does not have. DAYS("3/1/2024", "1/1/2024") is a locale-sensitive expression; DAYS(45352, 45292) is not.

Arguments

DAYS(end_date, start_date) — both required.

end_date — the later endpoint by convention, though nothing requires it to be later; a start after the end simply gives a negative result.

start_date — the earlier endpoint by convention.

Both arguments accept either a numeric serial or text. The documentation's own wording keeps the two paths separate: numbers are subtracted, text is parsed by DATEVALUE first. Microsoft also notes that the result is an integer without time components, so fractional serials contribute no fractional days.

The argument position most often misused is the first one. Because the function is named for a quantity rather than a direction, readers frequently write DAYS(start, end) and get a negative number that then flows quietly into a downstream calculation.

Try it

Quirks

Result and edge cases

Returns a Number: an integer count of days, negative when start_date is later than end_date.

• Equal dates give 0.
• Fractional serials do not produce a fractional answer; Microsoft's remark that the result carries no time component says the truncation happens, but the page does not say whether each argument is truncated before subtracting or the difference is truncated afterwards. Those two rules disagree — DAYS(2.4, 1.6) is 1 under the first and 0 under the second. This is not settled here.
• Straddling the 1900 leap-year artefact. An interval whose endpoints lie on opposite sides of serial 60 counts the phantom 29 February 1900 as a real day. DAYS(61, 59) is 2 as arithmetic and one day too many as a calendar fact. See FUNC.DATE.
• Mixed numeric/text arguments take one path each, per the documented rule.

Errors

As documented on the Microsoft page:

• #NUM! when numeric date arguments fall outside the range of valid dates.
• #VALUE! when a text argument cannot be parsed as a valid date.

An error value passed as either argument propagates in the ordinary way.

How to implement

Notes for implementers

1. Implement the two coercion paths separately and visibly. A single "coerce to number" front-end will silently give DAYS the wrong behaviour for text, because ordinary numeric coercion does not parse "1-Jan-2024" and DATEVALUE does.
2. Pin the truncation order before writing tests, and state it in the implementation, since the documentation does not.
3. Do not "fix" the leap-year artefact here. The count is defined on serials; correcting for the phantom day would make DAYS disagree with subtraction, which is the one thing the documentation guarantees it does not do.

Rust code

9 days_fn "DAYS" (2, 2) [V "end_date", V "start_date"] |s, a, sv| {
    if let Some(code) = a.iter().find_map(CalcValue::as_error) {
        return fail(code);
    }
    let end = date_arg(s, a, 0)?;
    let start = date_arg(s, a, 1)?;
    out(s, k::days(end, start))
}

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

• The − operator. end − start on two date serials gives the same number for numeric arguments and is not equivalent for text arguments, because − uses the engine's ordinary text-to-number coercion while DAYS is documented to use DATEVALUE. This is the sharpest reason to prefer DAYS when inputs may be text — and to prefer − when they may not, since DAYS then buys nothing but a locale dependency.
• DATEDIF with unit "D" — the same count, but with start-first argument order and with a documented #NUM! when start is after end, where DAYS simply goes negative.
• DAYS360 — an interval in accounting days, not calendar days; the two functions answer different questions and are routinely confused.
• NETWORKDAYS — calendar days minus weekends and holidays.
• DAY — the near-homonym, and unrelated: day-of-month of a single date.

Named variants

No named variants are recorded yet.