Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
add 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, 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
share|improve this answer
1  
Do elaborate from where this arbitrary starting date of December 29, 1899 comes from? –  Celeritas Jul 4 '13 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. –  matan129 Jul 4 '13 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? –  Celeritas Jul 4 '13 at 17:25
    
Excel 2013 does this too, though it outputs 41421 (might be timezone stuff) –  Szymon Szydełko Jul 4 '13 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. –  matan129 Jul 4 '13 at 17:29
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.