Stannum/blog/

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:

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.