During our migration we had a lot of issues with date formats. The old system didn't save its date as a type and as such caused us all sorts of trouble. The key to getting the dates working was to apply a cryptic yet simple formula to the date values in Excel.

The original Date Format was stored in Unix Epoch time. This is calculated as the number of seconds elapsed from 01/01/1970. Excel calculates the dates as the number of days from 01/01/1900.

The date value stored in the old database looked like this:

1171862018

1171807304

1158543880

In order to convert we apply some basic math. First we convert the seconds into days:

Divide 1171862018 by 86400 this will give you:

13563.2178 Or the number of days since 01/01/1970. Now of course were missing 70 years of days to make this date right. So we add 25569 to get a total of:

39132.2178.

Now just plug that value into an Excel cell and format as a date and you'll get 2/19/2007

Here is a look at the data as it goes through each step in the process: