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.

No comments: