YEAR
Quirk foundCategory: Date and time · Last tested 2026-09-01
Real compatibility results for the YEAR function: executed in Excel for the web, Google Sheets and LibreOffice Calc, with desktop Excel behavior from Microsoft’s official documentation (we do not run desktop Excel — Excel for the web is a different application and is executed separately). Syntax and links to that documentation are below.
Support matrix
| Engine | Documented | Live-tested | Verdict |
|---|---|---|---|
| Excel (desktop) | Yes | No — documented only | n/a |
| Excel for the web | — | Yes (recalc, 2026-09-01) | Supported, behaves as documented |
| Google Sheets | Yes | Yes (Drive import, 2026-08-29) | Quirk found |
| LibreOffice Calc | Yes | Yes (25.8.7.3, 2026-07-29) | Quirk found |
LibreOffice version history
We executed the same test cases under each LibreOffice release to show exactly when YEAR’s support changed — not documentation claims, real results.
| LibreOffice version | Verdict | Tested |
|---|---|---|
| 24.2.0.3 | Quirk found | 2026-08-29 |
| 24.8.7.2 | Quirk found | 2026-08-29 |
| 25.2.0.3 | Quirk found | 2026-08-29 |
| 25.8.7.3 | Quirk found | 2026-07-29 |
Why isn't YEAR working in LibreOffice?
YEAR exists in LibreOffice 25.8.7.3, but it is not a drop-in match for
Excel — our executed tests found real behavioral differences (detailed in the test results on this
page). If a formula that works in Excel or Google Sheets misbehaves in LibreOffice, compare your usage
against the failing cases above before assuming your data is wrong.
Why isn’t YEAR working in Google Sheets?
YEAR runs in Google Sheets, but our executed cases show it does not match
Excel’s documented behavior on every input (the failing cases are listed on this page). If a
formula that behaves one way in Excel gives you a different answer in Sheets, compare your usage
against those cases before assuming your data is wrong.
Discovered quirks
-
=YEAR(1) on
Google Sheets returned
1899, but the documented/expected
result is 1900.
Provenance
Serial 1 is Jan 1, 1900 in the 1900 date system; MISMATCH vs expected: expected 1900, got 1899
-
=YEAR(1) on
LibreOffice Calc returned
1899, but the documented/expected
result is 1900.
Provenance
Serial 1 is Jan 1, 1900 in the 1900 date system; MISMATCH vs expected: expected 1900, got 1899
Executed test cases
Excel for the web (executed 2026-09-01 via OneDrive recalculation)
These values come from Excel for the web, not from desktop Excel. They are two different implementations of the calculation engine, and this run measured only the web one: the corpus was uploaded to OneDrive as .xlsx, recalculated by Excel for the web on open, and downloaded again for readback. Excel for the web is a rolling service with no pinnable version, so the run is identified by its date. Where a value here disagrees with the Expected column — which is Microsoft’s documentation of the desktop product — we cannot tell you whether the web engine diverges from the desktop one or the documentation is wrong about both, because we do not run desktop Excel.
| Formula | Description | Result | Expected | Verdict |
|---|---|---|---|---|
| =YEAR(DATE(2024,3,15)) | YEAR applied to a constructed date | 2024 | 2024 | Matched |
| =YEAR(1) | YEAR applied directly to the raw serial number 1 (the classic Excel epoch, serial 1 = Jan 1, 1900) | 1900 | 1900ProvenanceSerial 1 is Jan 1, 1900 in the 1900 date system |
Matched |
| =YEAR(45366.75) | A serial with a fractional (time-of-day) component still resolves to the correct calendar year | 2024 | 2024Provenance45366 = 2024-03-15; the .75 fractional day (18:00) is ignored by YEAR |
Matched |
Google Sheets (executed 2026-08-29 via Drive import)
Google Sheets is a rolling service with no pinnable version, so this run is identified by its date. The corpus was imported to Drive as .xlsx, recalculated by Sheets, and exported back for readback.
| Formula | Description | Result | Expected | Verdict |
|---|---|---|---|---|
| =YEAR(DATE(2024,3,15)) | YEAR applied to a constructed date | 2024 | 2024 | Matched |
| =YEAR(1) | YEAR applied directly to the raw serial number 1 (the classic Excel epoch, serial 1 = Jan 1, 1900) | 1899 | 1900ProvenanceSerial 1 is Jan 1, 1900 in the 1900 date system |
Mismatch |
| =YEAR(45366.75) | A serial with a fractional (time-of-day) component still resolves to the correct calendar year | 2024 | 2024Provenance45366 = 2024-03-15; the .75 fractional day (18:00) is ignored by YEAR |
Matched |
LibreOffice Calc 25.8.7.3 (tested 2026-07-29)
| Formula | Description | Result | Expected | Verdict |
|---|---|---|---|---|
| =YEAR(DATE(2024,3,15)) | YEAR applied to a constructed date | 2024 | 2024 | Matched |
| =YEAR(1) | YEAR applied directly to the raw serial number 1 (the classic Excel epoch, serial 1 = Jan 1, 1900) | 1899 | 1900ProvenanceSerial 1 is Jan 1, 1900 in the 1900 date system |
Mismatch |
| =YEAR(45366.75) | A serial with a fractional (time-of-day) component still resolves to the correct calendar year | 2024 | 2024Provenance45366 = 2024-03-15; the .75 fractional day (18:00) is ignored by YEAR |
Matched |
Docs & syntax
- Excel (desktop): official documentation
- Google Sheets: official documentation
- LibreOffice Calc: official documentation
Related how-to recipes
- How to get the day number of the year (1-365)
- How to get the year, month, or day from a date
- How to create a quarter-and-year label like "Q3 2026"
- How to get the quarter-end date for any date
- How to sum values by month
- How to calculate a year-to-date (YTD) total
Where YEAR behaves differently
- DAY(1), MONTH(1) and YEAR(1): Excel disagrees with Sheets and LibreOffice
Serial number 1 is Jan 1 1900 in Excel but Dec 31 1899 in both Google Sheets and LibreOffice (executed), so DAY/MONTH/YEAR of a raw serial silently disagree. Why, and how to migrate.