EDATE

What it does

EDATE(start_date, months)

EDATE moves a date by a whole number of months, keeping the day-of-month. The serial it returns denotes the day with the same day-of-month, in the month months steps away from start_date's month.

The interesting part of that sentence is what it does not say, and the documentation does not say it either. When the target month has no such day-of-month — 31 January offset by one month, or 29 February offset by twelve — some rule has to apply. Calendars generally clamp to the last day of the target month, and EDATE is widely understood to clamp. Microsoft's EDATE page does not state a rule for that case, and this Handbook has not verified Excel's behaviour. It is stated here as the expected behaviour and nothing stronger.

That gap is the whole reason EDATE is not simply DATE(YEAR(s), MONTH(s)+n, DAY(s)). DATE carries excess days forward — its documented rule turns a 31st in a 30-day month into the 1st of the next month. If EDATE clamps, the two expressions disagree on exactly the dates people build payment schedules from. If it does not clamp, EDATE is redundant. Either way, the answer is load-bearing and it is not written down.

Arguments

EDATE(start_date, months) — both required.

start_date — the date to offset. Microsoft's page repeats the category guidance: supply dates "by using the DATE function, or as results of other formulas or functions", not as text.

months — "the number of months before or after start_date. A positive value for months yields a future date; a negative value yields a past date." The page states the conversion rule explicitly: "If months is not an integer, it is truncated." Truncation, not rounding — so EDATE(s, 1.9) is one month, and EDATE(s, -1.9) is, on the ordinary meaning of truncation, minus one month rather than minus two. The behaviour of truncation on negative values is the part worth testing, because languages disagree about it.

Try it

Quirks

Result and edge cases

Returns a Number: a whole date serial, with no time component.

• Day-of-month preservation is the defining behaviour — and the end-of-month case is the undocumented one described above.
• months = 0 returns the start date's own serial (with any time component dropped), which makes EDATE(s,0) a serviceable "floor to whole day" idiom.
• Fractional months is truncated, per the documented rule.
• Fractional start_date — the page does not say whether the time component is truncated before or after the offset. For whole-month arithmetic the two agree, so this is a low-stakes gap, but it is a gap.
• The 1900 boundary. An offset that lands below serial 1 is out of range; see FUNC.DATE for the serial model, and note that months landing in January or February 1900 interact with the phantom leap day.

Errors

As documented on the Microsoft EDATE page:

• #VALUE! when start_date is not a valid date.

That is the only condition the page states — and it is worth noticing that the sibling EOMONTH page documents #NUM! for the corresponding situation, plus a second #NUM! when start_date plus months yields an invalid date. Two functions of identical shape, documented with different error values for the same failure. Whether Excel actually behaves differently, or whether one of the two pages is simply wrong, is an open documentation discrepancy that nobody has resolved against a running Excel for this Handbook. It is exactly the kind of divergence claim-language rule 5 exists to keep visible rather than silently harmonized.

EDATE's page does not document what happens when the result is out of range, though EOMONTH's does for the same operation.

How to implement

Notes for implementers

1. Write the end-of-month rule down explicitly, whichever way you implement it. It is the one behaviour of this function that a reader cannot infer from the documentation, and therefore the one a compatibility consumer most needs stated.
2. Truncate months toward zero, matching the documented word "truncated" — do not floor. -1.9 truncates to -1 and floors to -2; the two give different answers for every negative fractional offset.
3. Do the arithmetic in (year, month) space, not by adding approximate day counts. Convert the serial to (y, m, d), add months to a zero-based month index with a floor-division carry, then reconstruct — clamping d at the target month's length if that is the chosen rule.
4. Do not assume the sibling's error value. Until the #VALUE!/#NUM! discrepancy above is resolved, an implementation that shares one error path between EDATE and EOMONTH is making an unverified compatibility bet.

Rust code

14 edate_fn "EDATE" (2, 2) [V "start_date", V "months"] |s, a, sv| {
    let start = strict_date_arg(s, a, 0)?;
    let months = strict_num(s, a, 1)?;
    serial_out(s, k::edate(start, months))
}

pub(crate) fn edate(serial: f64, months: f64) -> Result<f64, W> {
    let serial = whole_serial(serial)?;
    let months = whole(months)?;
    let (year, month, day) = ymd_1900(serial);
    let (year, month) = shifted_month(year, month, months)?;
    if !(1900..=9999).contains(&year) {
        return Err(W::Num);
    }
    let day = day.min(days_in_month(year, month));
    excel_serial_from_ymd(DateSystem::System1900, year, month, day).ok_or(W::Num)
}

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

• EOMONTH — the same month offset, but landing on the last day of the target month instead of preserving the day-of-month. EOMONTH(s, n) and EDATE(s, n) coincide exactly when s is already a month end and the target month is no shorter.
• DATE — the constructor whose month rollover is the carrying alternative to EDATE's (expected) clamping.
• WORKDAY — offsetting by working days rather than months.
• DATEDIF with unit "M" — the inverse question: how many whole months lie between two dates.
• Confusable. EDATE and EOMONTH are near-interchangeable in appearance and never in meaning; and EDATE(s, 12) is not the same as DATE(YEAR(s)+1, MONTH(s), DAY(s)) if the clamping hypothesis holds and s is 29 February.

Named variants

No named variants are recorded yet.