Configurable implementation of Excel's handling of < 1900 dates

[swmal]

EPPlus uses .NET OADate functions to serialize/deserialize dates. Excel has a different behaviour, see below.

Microsoft Excel has, since its earliest versions, incorrectly considered 1900 to be a leap year, and therefore that February 29 comes between February 28 and March 1 of that year. The bug originated from Lotus 1-2-3, and was purposely implemented in Excel for the purpose of backward compatibility. Microsoft has written an article about this bug, explaining the reasons for treating 1900 as a leap year.[7] This bug has been promoted into a requirement in the Ecma Office Open XML (OOXML) specification.[8][9]

_{^{Source: Wikipedia}}

In Excel, dates are numbers, called date serial numbers, which are formatted in special ways. In Excel spreadsheets, date serial number 1 represents January 1, 1900. But in VBA, date serial number 1 represents one day earlier: December 31, 1899. Lotus 1-2-3 can be blamed for this one-day discrepancy, because Lotus didn’t handle leap years correctly.

Our calendar adds a 29th day to February in all years evenly divisible by 4, except for those ending in 00. These years receive the extra day only if they are evenly divisible by 400. Therefore, 1600, 2000 and 2400 are leap years but 1700, 1800, 1900 and 2100 are not.

Unfortunately, the designers of Lotus 1-2-3 overlooked these facts and included the date February 29, 1900, in their version the DATE function. So when Microsoft introduced Excel for Windows 1.0 in 1987, Excel’s designers had to faithfully mimic the incorrect performance of the market-leading spreadsheet, Lotus 1-2-3. Therefore, Excel’s DATE function also includes that non-existent date.

_{^{Source: exceluser.com}}

We could implement this bug/behaviour and make it configurable. The current EPPlus behaviour should be default.