Zero date in Calc, metaquestions

[LobaLuna]

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.

[mikekaganski]

My First BillG Review

[Lupp]

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 suggestion.
Read Calc: format date YYMMDD, paste YYMMDD data, extend YYMMDD cells to better understand my point.

[mikekaganski]

And Eric’s Complete Guide to VT_DATE | Fabulous adventures in coding

[Lupp]

Less ingenious, but probably usable: How to get UTC DateTime in a spreadsheet or in Basic (View topic) • Apache OpenOffice Community Forum

[Villeroy]

Leap_Year_Bug.ods (30.3 KB) explaining Excel’s leap year bug and why 1899-12-30 is compatible with Excel’s day #1 1900-01-01.

[LobaLuna]

Thank you, Villeroy. The ODS is crystal clear.
It is so amazing how much history lies behind a single (yet very interesting) fact.

[Wanderer]

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.

J.

[Lupp]

You may also be interested in the attached example. 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”?

[LobaLuna]

No, @Wanderer, I’m not trying to reinvent nothing. As you can read, I’m more thinking than doing something. This is kinda rethorical question, but you missed the point that a real day-one should be January 1, 1901 for the first day of the century. That what started my stream of thoughts.

Thanks for input, anyway.

[LobaLuna]

I totally agree with you: there must be an international way of communicate dates and that should be the ISO way.

Thanks for all your input. It was very interesting!