Handling Excel Date & Time Formats

Excel Dates. When exporting an Excel worksheet to a CSV, dates are normally exported in a human readable format that will be automatically identified by Differencia.

Internally Excel stores dates as a number of days since a fixed date, with the time of day being stored as a decimal fraction. Windows and Mac Excel use different base dates. For example, in Excel for Windows, "3rd July 2002 3:00:00 PM" has a value of 37440.625.

Some applications export dates in the Excel date format. Differencia can correctly interpret these numbers as dates & times.

To use Excel Date & Time formats:

Open the file containing the Excel format dates in the Comparison Definition view by choosing File > Open...
Hit the Edit button next to the newly created Layout
In the Layout View click on the column containing the Excel date format
The field should have been interpreted as a Number. In the Field Details panel, change the Data Type to "Date / Time"
In the Format field enter "X" for Excel for Mac dates (1 Jan 1904 base date) or "x" for Excel for Windows dates (1 Jan 1900)
Include the field as normal in Date & Time comparisons

Important Notes

Note that there is a bug in Excel for Windows, inherited from Lotus 1-2-3, whereby 1900 is treated as a leap year. For the "x" format, Differencia treats both the numbers 59 & 60 as 28th February 1900. Subsequent dates are correctly interpreted.

Negative numbers in this field are interpreted as valid dates prior to 1900 or 1904 as appropriate. However these are not regarded as valid dates in Excel. This may cause issues if negative values are used in the source system to represent an invalid or empty date.

Handling Excel Date & Time Formats

Important Notes

See also