Convert Unix dates for export to Excel with PHP or MySQL
Its easy enough to export formatted dates as strings to a .csv file for Excel, but if you want to be able to re-format the dates in Excel you need to export the “serial date/time” value used by Excel. This is the fractional number of days since the mythical 0-Jan-1900 epoch.
Unix timestamps are in UTC (GMT), so the first thing we need to do is convert to local time by adding the UTC to local time difference. This is complicated by the time difference varying for daylight saving. Fortunately, the PHP date function will provide this value in seconds for a given date:
|
1 |
date('Z',$localdate) |
As the Unix timestamp is the number of seconds since a certain date, we need to convert that number to days. This is done by dividing by the number of seconds in a day: 24 x 60 x 60 = 86400.
Finally we need to add the number of days in the 70 years between the serial date/time epoch and the unix timestamp epoch of 01-01-1970. Allowing for leap years, this comes to 25569 days. (This can be verified by entering 01-01-1970 into an Excel cell, and formatting the cell as “General”.)
Putting all this together gives a function like this:
|
1 2 3 4 5 |
function excel_date($input) { $output = 25569 + (($input+date('Z',$input)) / 86400); return $output; } |
And for completeness, here’s a function to go back the other way and convert Excel dates to Unix dates:
|
1 2 3 4 5 6 |
function unexcel_date($input) { $output=($input-25569)*86400; $output=$output-date('Z',$output); return $output; } |
If you are extracting the Unix times from a MySQL database, you can do the conversion in the MySQL query:
|
1 |
select (25569+((UnixDate+(10*3600)/86400)) as UnixDate |
Where the ’10’ is the number of hours your timezone is ahead of UTC. Unfortunately I haven’t worked out how to get MySQL to determine the timezone difference.