Monday, December 08, 2014

Libreoffice and default Microsoft Excel 1900 Date System

The custom date in format m/d/yy is not formatted in libreoffice but instead a number is shown. This number corresponds to the serial day starting at January, 1 1900. So 5 will correspond to 1905. But there is a leap year bug for which a correction needs to be made (if (serialNumber > 59) serialNumber -= 1) as you can see in action in this runnable.

So if you convert excel to CSV for example and you get a number instead of an expected date, go to that Excel file from the libreoffice GUI and convert a cell to Date to see if the output makes sense as a date. At that point, convinced that those are indeed dates all you need to do is apply the algorithm to the numbers to convert them to dates in the resulting CSV.