On spreadsheets, date and time calculations are important. What about numeric representations?
Today I entered 2026-may-29 on a LibreOffice Calc spreadsheet, then pressed Enter. It self-converted to 2026-05-29. Next, I clicked “Format as Date.” It answered with 46171.
I wondered how it was getting that number, so looked it up. Apparently, by default in LibreOffice Calc, Day 0 was Dec 30, 1899. Every day since increments the date by 1. Therefore, Jan 1, 1900, as a numeric date, is 2. Jan 1, 1901, 365 days later, is 367, and so on. (1900 was not a leap year, believe it or not. I was surprised, too; that’s for another post.)
On spreadsheets, dates are normally compared with other dates, so the “zero reference” might not matter too much, so long as it’s consistent. However, Excel’s day 1, apparently, is January 1, 1900 (whereas LibreOffice thinks that’s day 2, it seems).
Therefore, one wonders: how compatible are LibreOffice Calc and Excel spreadsheets? Well, it seems that a LibreOffice Calc sheet can be saved in xlsx format. What about changing the default start date? Apparently, one can do that as well.
This post is spilling in numerous directions; I plan to follow up. For now, at least, it seems clear how 2026-05-29 becomes 46171 on a LibreOffice Calc spreadsheet.
Source:
LibreOffice 26.2 Help: Date and Time Functions