search

Quartz / bad-data-guide

An exhaustive reference to problems seen in real-world data along with suggestions on how to resolve them.
4.05k stars 404 forks source link

Excel dates #2

Open calvinmetcalf opened 8 years ago

calvinmetcalf commented 8 years ago

Arn't they actually December 31st 1899 or something similar slightly off. Due to leap year bug or something.

hydrosquall commented 8 years ago

@calvinmetcalf could you post an example of the issue you're describing (is it this one?)

bdcaf commented 7 years ago

Yes the dates are stored as number counting from "1899-12-31" (is day 1).
Seems the didn't consider that if year is divisible by 100 it is not a leap year.

Fun fact - there is a second date system used by excel for mac which starts on "1904-01-01" (day 1) (https://support.microsoft.com/en-us/help/214330/differences-between-the-1900-and-the-1904-date-system-in-excel - interesting they don't mention the leap year bug there... - but surprisingly the difference is 4 years and 1 day)