Invalid date gets converted to 12/30/1899

[marciekaiser]

My Excel file has a cell with the value "1/0/1900". When looping through and reading in the values, EPPlus shows cell.Text and cell.Value as "12/30/1899". Even though the 1900 date is not valid, I want to read in EXACTLY what is in the file, and I can't find a way to do that.

[retailcoder]

This is the LOTUS-123 date bug, which Excel replicated to maintain backward compatibility with the then-competing software. The value of the cell isn't "1/0/1900" - the Value is (well, should be - I'm not familiar with this library) 0, and its Text (string representation) is "1/0/1900".

Note that VBA does not have this bug: ?Format(CDate(0), "yyyy-mm-dd") (correctly) outputs 1899-12-30. To "fix" this (or "break" it, depending on whether you see introducing a deliberate bug as a fix), I would presume ExcelRangeBase.GetDateText needs to be modified to return a hard-coded (?) "1/0/1900" string given a DateTime value of 1899/12/30... haven't looked at the unit tests, but this will quite likely break stuff.