Converting Excel date/time to Unix timestamp

Timestamp conversion are required when you import information generated by Windows based application to UNIX system. I faced a similar type of problem when I was trying to read data from a MS Excel file and storing it in MySQL database using a php code.

I was using Excel_reader class to read the Excel file. In that file there were two columns for 2 dates. While reading those column value I found they contain some numeric value. Those values are not same as unix timestamp value. As I was trying to store those values as timestamp I need to convert them into the timestamp.

After some searching and reading from internet I found that Windows and Unix system stores timestamp in different manner. Once this is clear to me I have converted them easily. So let us understand how these two system stores timestamp .

In Unix system timestamp is stored as integer in terms of seconds. If we consider the current timestamp value in Unix then it is basically the no. of seconds passed from 1 January 1970 00.00Hrs to till now. One important thing for Unix timestamp is that it is measured with reference to GMT time. That means there is no time-zone adjustment considered in case of Unix timestamp.

In case of Windows time it is stored as floating point/real number but in terms of days. For current timestamp, the no. days spent from 1 January 1900 stored in integer part and fraction of the day stored in the fractional part of the timestamp.

Now comes the conversion process. I made a simple conversion calculation as follows:

  1. $DayDifference = 25569; //Day difference between 1 January 1900 to 1 January 1970
  2. $Day2Seconds = 86400; // no. of seconds in a day
  3. $ExcelTime //integer value stored in the Excel column
  4. $UnixTime = ($ExcelTime $DayDifference)*$Day2Seconds;

Now if you look at the calculation, you may find that the conversion is not absolutely accurate. This is because I haven’t consider the fractional part of the Excel time. The reason behind doing so is that all I need to do is to convert a date from Windows format to Unix format. So fraction part of windows time won’t affect a lot for my requirement. But if you use this calculation for converting a date-time from Windows format to Unix format then conversion of the fractional part should done.
If you like this small tips please let me know about it.

4 Comments

  1. Smitty says:

    Thank you for this article! It has greatly simplified a data import for me today.

    I dropped this in to my spreadsheet and then converted one of the Unix timestamps back into a date (using http://www.unixtimestamp.com/) but the date was off by one day. Just to double-check the math, I entered a formula in a cell to find the difference between 1/1/1970 and 1/1/1900. The result? 25568–one less than the $DayDifference you specify above.

    Making that one change resulted in a Unix timestamp that, when converted back to mm/dd/yyyy matched the original.

  2. Susenjit says:

    Thanks for checking. However I used this formula to convert Excel date to Unix timestamp. I had written a program which picks up a cell(containing a date) value and then convert it in Unix timestamp. I found the result was correct.

    After getting your post I just made another test. You can check it yourself. Put 1/1/1970 and 1/1/1900 in 2 cells now change the format of these cells as number. What you will get is that first cell value as 25569 and second cell value as 1. But while calculating the day difference you should consider 1/1/1900 as day 0.

    However using $DayDifference as 25568 I converted a Excel date to Unix timestamp and print that using strftime(). I got one day more rather than the expected result.

  3. Scott Reynolds says:

    Don’t forget to account for UTC offset. Excel dates are specified using local time, and UNIX timestamps are UTC.

  4. Susenjit says:

    Thanks Scott Reynolds for your valuable input.

Leave a Reply