YEAR

What it does

YEAR(serial_number)

YEAR projects a date serial onto its calendar-year component. Given a serial s, it discards the fractional part, finds the calendar day that ⌊s⌋ denotes under the workbook's date system, and returns that day's year as an integer in the range 1900–9999 (1904–9999 in a 1904-system workbook).

Arguments

YEAR(serial_number) — one required argument.

serial_number — the date whose year is wanted. Microsoft's documentation is emphatic across this whole category that dates should be supplied as serials, from DATE, from another formula, or from a cell already holding a date, and not as text, because text dates are locale-dependent and can be misparsed. YEAR accepts what the ordinary scalar coercion rules accept (see coercion and lifting): numbers, numeric text, logicals.

The commonly misunderstood point is that serial_number is a number, not a "date object". Any number in range is a legal argument, including numbers that no user would call a date.

Try it

Quirks

Result and edge cases

Returns a Number: a four-digit year.

• Fractional serials. The time-of-day component is discarded. YEAR(43831.75) is the same as YEAR(43831).
• Serial 0. Under the 1900 system, serial 0 is the phantom "0 January 1900". Whether Excel reports 1900 for it is an artefact question, not a calendrical one — see below.
• Serial 60. The phantom 29 February 1900. YEAR returns 1900 for it under any sane reading, so it is the least diagnostic of the three decomposers here; DAY is where the artefact shows.

Errors

As documented for this category, and on the page linked below:

• #NUM! when serial_number is out of range for the workbook's date-base value — negative serials being the clear case.
• #VALUE! when the argument cannot be converted to a number (ordinary coercion behaviour, not a YEAR-specific rule).
• An error value passed in propagates.

How to implement

Notes for implementers

1. Floor, do not truncate. The fractional part must be removed by flooring so that the behaviour is defined the same way for the (error-bound) negative region as for the positive one. Truncation and flooring differ exactly where the range check is supposed to fire.
2. The year is not derivable by division. No 1900 + s/365.25-style shortcut is correct; the mapping has to go through a real calendar, adjusted for the leap-year artefact.
3. The date system is context, not a constant. See FUNC.DATE.

Rust code

3 year_fn "YEAR" (1, 1) [V "serial_number"] |s, a, sv| {
    of_date(s, a, |x| k::serial_to_ymd(x).map(|(y, _, _)| y as f64))
}

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

• MONTH, DAY — the other two components of the same decomposition. Together with YEAR they invert DATE.
• DATE — the constructor. DATE(YEAR(s),MONTH(s),DAY(s)) should return ⌊s⌋; that round trip is the obvious metamorphic test for this family.
• YEARFRAC — despite the shared prefix, unrelated in kind: YEARFRAC measures an interval in years, YEAR names a year.
• DATEDIF with unit "Y" — counts complete years between two dates, which is not YEAR(b) − YEAR(a). The difference of years and the number of elapsed years disagree whenever the anniversary has not yet passed; this is the most frequent age-calculation bug in spreadsheets.

Named variants

No named variants are recorded yet.