15
1
Given a non-negative integer Excel-style date code, return the corresponding "date" in any reasonable form that clearly shows year, month, and "day".
Trivial, you may think. Did you notice the "scare quotes"? I used those because Excel has some quirks. Excel counts days with number 1 for January 1st, 1900, but as if 1900 had a January 0th and a February 29th, so be very careful to try all test cases:
Input → Output (example format)
0 → 1900-01-00 Note: NOT 1899-12-31
1 → 1900-01-01
2 → 1900-01-02
59 → 1900-02-28
60 → 1900-02-29 Note: NOT 1900-03-01
61 → 1900-03-01
100 → 1900-04-09
1000 → 1902-09-26
10000 → 1927-05-18
100000 → 2173-10-14
1Does every year have a 0th of January and 29th of February or is 1900 the only anomaly? – Shaggy – 2018-11-27T22:24:05.790
41900 is the anomaly. Excel treats leap years correctly except for 1900 (which is not a leap year). But that was for compatibility with Lotus 1-2-3, where the bug originated. – Rick Hitchcock – 2018-11-27T22:31:53.930
@Shaggy Only 1900 is anomalous. – Adám – 2018-11-27T22:38:22.117
3@RickHitchcock Apparently, the Lotus 1-2-3 devs did it to save on leap year code, such that the rule simply became every fourth year. With good reason too; 1900 was far in the past, and 2100 is, well, in a while. – Adám – 2018-11-27T22:41:29.723
1If 1900 is the only anomaly in Excel, but Lotus 1-2-3 treated all years divisible by 4 as leap years, then you have to wonder why Microsoft decided to make that one exception. (But every day I ask why Microsoft made some inane decision.) – Rick Hitchcock – 2018-11-27T22:49:59.240
3@RickHitchcock It may very well be that the original Lotus 1-2-3 couldn't handle Y2K, and so Microsoft decided to mimic that one issue, but otherwise stay right. Btw, the legacy lives on: .NET's OADate has epoch 1899-12-*30* so that it will line up with Excel on all but the first two months of 1900, however this necessitates the
DayOfWeek
method because the original epoch, 1899-12-30 (or the fictive 1900-01-00) was chosen such that the weekday simply was the mod-7 of the day number, but that won't work with 1899-12-30. – Adám – 2018-11-27T23:09:34.2074
Here's the story behind the "why" about Excel dates: Joel on Software: My First BillG Review. Informative (and entertaining) read.
– BradC – 2018-11-28T16:31:14.230