EOMONTH

What it does

EOMONTH(start_date, months)

EOMONTH offsets a date by a whole number of months and returns the serial of the last day of the resulting month. The day-of-month of start_date is discarded entirely: only its year and month matter.

Precisely: let (y, m) be the year and month of start_date; add months to m with carry into y; return the serial of the last day of that (y, m). EOMONTH(s, 0) is therefore "the end of this month" and EOMONTH(s, -1) is "the end of last month", which is why the function turns up in almost every period-boundary formula in finance.

Two identities follow, and both are used constantly:

• DAY(EOMONTH(s, 0)) is the number of days in s's month.
• EOMONTH(s, -1) + 1 is the first day of s's month.

The second is the idiomatic "start of month" expression precisely because Excel has no BOMONTH.

Arguments

EOMONTH(start_date, months) — both required.

start_date — "a date that represents the starting date". Microsoft repeats the category guidance that dates "should be entered by using the DATE function, or as results of other formulas or functions" rather than 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 is toward zero on the ordinary reading of the word, which makes the negative fractional case (months = -1.5) the one worth testing.

The argument that is misunderstood is start_date, and specifically the belief that its day-of- month matters. It does not: EOMONTH(DATE(2024,3,1), 0) and EOMONTH(DATE(2024,3,31), 0) are the same value.

Try it

Quirks

Result and edge cases

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

• February. EOMONTH is one of the few places in the category where correct leap-year handling is directly observable in ordinary use — and where the 1900 artefact is directly reachable. EOMONTH of a February 1900 date asks whether the implementation believes in the phantom 29 February; see FUNC.DATE.
• months = 0 does not return start_date; it returns the month end. Any time component of start_date is not carried into the result.
• Result out of range is documented as an error, unlike on the EDATE page — see below.
• Very large months simply carries through years; the range check is on the result.

Errors

As documented on the Microsoft EOMONTH page:

• #NUM! when start_date is not a valid date.
• #NUM! when start_date plus months yields an invalid date.

Note the discrepancy with the sibling: EDATE's page documents #VALUE! for an invalid start_date, and documents no result-out-of-range condition at all. Two functions of the same shape, taking the same arguments, documented with different error values for the same failure. This Handbook does not silently pick a winner: the divergence is stated, and resolving it against a running Excel is listed below as an unrun experiment. See claim language, rule 5.

#VALUE! from an argument that will not coerce, and propagation of an error argument, are the ordinary behaviours described in coercion and lifting.

How to implement

Notes for implementers

1. Compute in (year, month) space and take the month length last. Adding months to a zero-based month index with floor-division carry, then looking up the target month's length, is both simpler and less error-prone than any day-count approach.
2. February 1900 is the special case. If the implementation models the leap-year artefact as a coordinate shift (the recommendation on FUNC.DAY), then the "length of February 1900" question has to be answered deliberately: the real calendar says 28, Excel's serial line contains a 29th.
3. Truncate months toward zero, per the documented word.
4. Do not share an error path with EDATE until the documented #NUM!/#VALUE! discrepancy is resolved by observation.

Rust code

15 eomonth_fn "EOMONTH" (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::eomonth(start, months))
}

pub(crate) fn eomonth(serial: f64, months: f64) -> Result<f64, W> {
    let serial = whole_serial(serial)?;
    let months = whole(months)?;
    let (year, month, _) = ymd_1900(serial);
    let (year, month) = shifted_month(year, month, months)?;
    if !(1900..=9999).contains(&year) {
        return Err(W::Num);
    }
    excel_serial_from_ymd(DateSystem::System1900, year, month, days_in_month(year, month))
        .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

• EDATE — the same month arithmetic preserving the day-of-month rather than jumping to the month end. The two coincide when start_date is already a month end and the target month is no shorter.
• DATE — the hand-rolled equivalent is DATE(YEAR(s), MONTH(s)+n+1, 0), which works by DATE's documented day-underflow rule (day 0 of a month is the last day of the previous one). Anyone who has written that expression has been using DATE as an EOMONTH.
• DAY — DAY(EOMONTH(s,0)) is the days-in-month idiom.
• YEARFRAC — shares the end-of-February sensitivity: Microsoft documents that YEARFRAC "may return an incorrect result when using the US (NASD) 30/360 basis, and the start_date is the last day in February", which is exactly the kind of date EOMONTH produces. The two functions are frequently composed, so the interaction is real.
• WORKDAY — month ends are frequently adjusted to business days afterwards.

Named variants

No named variants are recorded yet.