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.
Add a comment
|
1 Answer
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, 1900which 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
-
1Do elaborate from where this arbitrary starting date of December 29, 1899 comes from? Jul 4, 2013 at 17:21
-
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.– matan129Jul 4, 2013 at 17:24
-
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? Jul 4, 2013 at 17:25
-
Excel 2013 does this too, though it outputs
41421(might be timezone stuff) Jul 4, 2013 at 17:28 -
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.– matan129Jul 4, 2013 at 17:29