The same serial number is a different date in LibreOffice than in Excel
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.
The surprise
Excel's serial 1 is 1 January 1900. LibreOffice's default serial 1 is 31 December 1899, one day earlier. Anything that reads day, month, or year straight off a bare serial inherits that one-day offset.
A minimal example
| Formula | Excel (documented) | Google Sheets | LibreOffice Calc 25.8.7.3 (executed) |
|---|---|---|---|
| =DAY(1) | 1 | Not yet executed | 31 |
| =MONTH(1) | 1 | Not yet executed | 12 |
| =YEAR(1) | 1900 | Not yet executed | 1899 |
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. We have not yet executed these cases in Google Sheets.
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.