DAY(1), MONTH(1) and YEAR(1) give a different date in Excel than in Sheets or LibreOffice

A raw date serial number resolves to a different calendar date under each engine's default settings, so =DAY(1), =MONTH(1) and =YEAR(1) return different results in LibreOffice Calc than the values Excel documents. There is no error; the date is simply off by the gap between the two epochs.

A minimal example

The Excel column holds the documented-expected values from our test corpus (serial 1 = 1 January 1900). The LibreOffice Calc 25.8.7.3 column is what our harness computed: serial 1 = 31 December 1899, so day 31, month 12, year 1899. Google Sheets was executed too — Drive import, 2026-08-29 — and it sides with LibreOffice, not Excel: DAY(1) is 31, MONTH(1) is 12, YEAR(1) is 1899. Excel for the web — a separate application from the desktop product, and the third engine we execute — sides with the documentation instead: recalculated on OneDrive on 2026-09-01 it returned DAY(1) = 1, MONTH(1) = 1 and YEAR(1) = 1900. So the split is not documentation against measurement but Microsoft against the rest: both Microsoft columns put serial 1 on 1 January 1900, while Google Sheets and LibreOffice count from 30 December 1899.

Why it happens

Excel uses the 1900 date system, in which serial number 1 is 1 January 1900 (with a well-known deliberate leap-year quirk earlier in that year). LibreOffice Calc stores dates as a day count from a configurable null date whose default is 30 December 1899, so serial 1 lands on 31 December 1899, one day before Excel's epoch anchor. That single-day offset is the whole cause. See Microsoft's date systems in Excel, and LibreOffice's null-date setting under Tools > Options > LibreOffice Calc > Calculate.

In practice this only bites when a formula is handed a bare serial number, or when a file carries a non-default null date (for example a workbook saved under the 1904 date system). Real dates entered with DATE() or the calendar display the same in both apps, because each engine maps the calendar date back through its own epoch consistently.

How to migrate safely

Never hand DAY, MONTH, or YEAR a bare serial. Wrap the actual date:

=DAY(DATE(2024,1,1)) returns 1 in every engine.

When importing, match the target's null date to the source before you rely on any serial arithmetic (LibreOffice: Tools > Options > LibreOffice Calc > Calculate > Date). Finally, re-check any hard-coded serial constants after migrating, since a shifted epoch moves every date built from a raw number.

Check before you migrate

A note on which Excel this is. The Excel column in the tables above is Microsoft’s documented behaviour for desktop Excel, as recorded in our test corpus — we do not run desktop Excel, and no value in that column is a measurement. Excel for the web is a different application with its own calculation engine, and that one we do run (recalculated on OneDrive, 2026-09-01). Its measured results are published on each function’s own page rather than in these guide tables. Because we have no desktop run to compare against, a disagreement between an Excel-web measurement and the documented column is genuinely ambiguous: it may mean the web engine diverges from the desktop one, or that the documentation is wrong about both. We do not claim to know which.