How does Open Office Calc make this conversion?

1

In a column, I have dates of the format 2013-05-27. When I selected "Default Formatting", the dates got changed to five digits, for example, 41422. Where does this number come from? It's not even subtracting the numbers.

Celeritas

Posted 2013-07-04T17:14:53.650

Reputation: 7 487

Answers

4

It's the number of days passed from Friday, December 29, 1899 and your date.

References & proofs:

  • Wolfram|Alpha - calculation proof
  • System time (Wikipedia) - note that on other systems / software this date is different.
  • Data types in VB - scroll down to "Notes for Date data type".
  • Microsoft Support - "Office spreadsheet" proof. Note that Excel's reference date is a day later after 29.12.1899: January 0, 1900 which equals to 30.12.1899.
  • Firebird Support - indirect proof, but when a value isn't entered the date turns to 30.12.1899
  • Epoch (reference date), Computing (Wikipedia) - another table of additional reference dates

matan129

Posted 2013-07-04T17:14:53.650

Reputation: 1 914

1Do elaborate from where this arbitrary starting date of December 29, 1899 comes from? – Celeritas – 2013-07-04T17:21:28.723

I don't know, it's just a common regulation. I don't think it has special meaning or something; I suppose the first developers to implement this just thought "it would be nice to begin from 1900" or something like this. – matan129 – 2013-07-04T17:24:52.223

Thanks I've never heard of this. Could you share a source of this information? Do other spreadsheets or other programs use this magic date? – Celeritas – 2013-07-04T17:25:50.863

Excel 2013 does this too, though it outputs 41421 (might be timezone stuff) – Szymon Szydełko – 2013-07-04T17:28:25.133

2 examples here and here. Also, according ton Wikipedia different systems calculate time relatively to different dates. By the way, my answer was an educated guess. I remembered something about "date-relative to.." so I checked it up on Wolfram|Alpha.

– matan129 – 2013-07-04T17:29:46.277

@SzymonSzydełko MS Excel does it relatively to the day after: January 1, 1900. proof

– matan129 – 2013-07-04T17:34:05.877

Well, Excel and OpenOffice being out-of-step in terms of day-numbers has to do with a deviation concerning whether or not 1900 is treated as a leap year. (Excel maintaining compatibility with Lotus 1-2-3 meant that it also had the leap-year bug.) – orcmid – 2013-07-04T19:44:26.797

I think @orcmid is correct. See http://www.joelonsoftware.com/items/2006/06/16.html

– a CVn – 2013-07-05T13:24:28.113

Asked: 2013-07-04T17:14:53.650

Viewed: 159 times

Active: 2013-07-06T11:10:49.587