[Solved] Why the base date is 1899-12-30 instead of 1899-12-31?

Discuss the spreadsheet application
Post Reply
luofeiyu
Posts: 21
Joined: Thu Sep 14, 2017 2:11 am

[Solved] Why the base date is 1899-12-30 instead of 1899-12-31?

Post by luofeiyu »

 Edit: Split unrelated question into new topic. -- MrProgrammer, forum moderator  

Why the default day is 1899-12-30 instead of 1899-12-31?Is there some historial story in it?
Last edited by MrProgrammer on Tue Nov 08, 2022 4:14 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved] -- MrProgrammer, forum moderator
LibreOffice 4.3.3.2 on Debian 8
User avatar
keme
Volunteer
Posts: 3707
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: How can convert now() into unix-timestamp?

Post by keme »

luofeiyu wrote: Tue Nov 01, 2022 1:35 pm Why the default day is 1899-12-30 instead of 1899-12-31?Is there some historial story in it?
The purpose is to minimize the error where data transfers between the "industry standard" spreadsheet apps (notably MS Excel) and Calc.

The earliest date Excel handles sensibly is 1900-01-01, which is "day 1" (not zero). This makes "Excel epoch" (day zero) 1899-12-31. However, Excel inherited an error from previous spreadsheet apps which assumed that 1900 was a leap year. The nonexistent 29th of February 1900 is counted in Excel time spans, just like it used to be in Lotus 123 and (IIRC) SuperCalc, and probably in most other relevant spreadsheet apps.

Calc uses a corrected calendar where 1900 is not a leap year. For date values to correspond between Calc and Excel, the "extra day" was catered for by pulling the epoch one day back, to 1899-12-30.

The most prominent consequence of this is that all dates before March 1900 will be one off when you transfer a spreadsheet with date entries between Excel and Calc. The error occurs for 60 dates or so, so a significant impact in some cases, but minimized. It is Excel which is in the wrong (with those other spreadsheet apps), and with negative time values (dates before epoch), Excel is totally lost anyway.
Last edited by keme on Tue Nov 08, 2022 9:46 am, edited 2 times in total.
User avatar
karolus
Volunteer
Posts: 1164
Joined: Sat Jul 02, 2011 9:47 am

Re: How can convert now() into unix-timestamp?

Post by karolus »

luofeiyu wrote: Tue Nov 01, 2022 1:35 pm Why the default day is 1899-12-30 instead of 1899-12-31?Is there some historial story in it?
the story is: 1900 was not a leap-year, but Excel does handle it as leap-year(for Compatibilty-reasons to its Predecessor) … with the zero-date one day earlier every date since 1900-03-01 has the same internal "Value" for both excel and AOO|LO
AOO4, Libreoffice 6.1 on Rasbian OS (on ARM)
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
User avatar
Villeroy
Volunteer
Posts: 31288
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How can convert now() into unix-timestamp?

Post by Villeroy »

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply