1900-01-00
2022-04-15 Permalink
Excel documentation says that:
[1900-01-01] is serial number 1, and [2008-01-01] is serial number 39448 because it is 39,448 days after [1900-01-01].
This is wrong, however. In fact 2008-01-01 is only 39446 days after 1900-01-01. The discrepancy is there for two reasons:
The person writing the documentation made a typical off-by-one error when counting from one. What they meant to say is that it is “serial number 39448 because it is 39,447 days after...”. This is why you should count from zero, kids.
Next, Excel incorrectly assumes that 1900 is a leap year, which it is not. Consequently its numbering scheme has a non-existing day of 1900-02-29.
If you happen to be unlucky enough to need to convert to/from Excel date representation, you should, instead, redefine 1899-12-30 as day zero in your code, counting backwards and forwards like you normally would. Unless you work at the Microsoft Excel team, that is, and thus required to preserve a buggy behavior for the sake of preserving buggy behaviors.
What’s about the title of this post though? Turns out that, for mysterious reasons, Excel formats day 0 as 1900-01-00. This is also when the universe was created, apparently. Any prior (i.e. negative) dates are invalid.