Ask Your Question

Zero date in Calc, metaquestions

asked 2021-04-13 20:19:04 +0200

Hi, everybody. This are more like a theoretical series of questions, not practical ones.

Why is not the zero date in Calc (or Excel) the very last day of the previous century? If 20th century ended on December 31st, 1900, this should be date zero, so in number format in Calc, date 1 ought to be January 1, 1901, the first day of the first month of the first year.

I see it even worse: zero date is not the last day of December, it is December 30. That doesn't make sense to me. The first day is last day of previous year!

Also I assume (possible wrong) that year 1899 was chosen on the belief that 1899 was the last year of the century, which is not correct. If there are historical, anecdotal reasons, I would love to understand them.

Apart, is there a chance that I assume/set a zero date according to what I just wrote above? For many date calculations, that would make my life easier in a accounting/clerk sense.

edit retag flag offensive close merge delete


I was told a guy at Lotus was once assigned (or decided it himself?) to make the last day of the 19th (not 20th!) century "day zero", and thus 1900-01-01 day one of the time scale for their software, but he missed to study the rules of the Gregorian calendar and thought 1900 was a leap year. Thus he counted back one day too far.
StarOffice 1.0 tried repairs by making 1900-01-01 "day zero", but didn't find friends for the idea. Then there is a NullDate of 1904-01-01 also used somewhere at some time, and the UTC NullDate of 1970-01-01. Humans simply are fond of confusion. Nowaday they ride their hobbies in a competion who might find the most absurd date format. This site is still waiting for their match. You see: Whats' most used nowadays in spreadsheets simpliy is the winner of the competition for the silliest ...(more)

Lupp gravatar imageLupp ( 2021-04-13 21:00:47 +0200 )edit

Less ingenious, but probably usable:

Lupp gravatar imageLupp ( 2021-04-13 22:11:19 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2021-04-13 21:21:23 +0200

Wanderer gravatar image

Your calculations should not assume anything on the position of zero for accounting. There are lots of Functions like DATEDIF wich usually do all necessary math. Otherwise you are bound in a very unusual system for accounting.

The start with January first 1900, was for compatibility with Lotus123. Microsoft quotes also, Exel runs under the false assumption of an existing Feb, 29 in 1900. ( German reference. )

To avoid this there is/was a second system, starting 1904, because from 1904 to 2196 you really have all 4 Years an additional day in February. This was the default in Excel for Macintosh for several Years, and can be selected per Excel/Calc-File.

I guess when you try to fix the false Feb, 29 in 1900, without breaking existing Excel-Sheets andbtry to retain the dates even over copy and Paste/clipboard you will just move "day zero" by one.

Conclusion: Don't be the next one reinventing the wheel, if it is not really necessary.


edit flag offensive delete link more


You may also be interested in the attached example. C:\fakepath\nullDayGame.ods

I regard this not only funny but potentially dangerous. There simply seems to be a too large majority of users having no idea of the implications.

BTW DATEDIF() also seems to be one of the spreadsheet-only fiunctions from the MS universe... What makes the diference? A second "f"?

Lupp gravatar imageLupp ( 2021-04-13 22:06:05 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2021-04-13 20:19:04 +0200

Seen: 23 times

Last updated: Apr 13