Covert UNIX Epoch dates in Microsoft Excel – Including Timezone Examples
Posted on January 22nd, 2009 | 9 Comments »
There are many sites out there that will give you a full description of what the UNIX Epoch standard date system is, why it is used, and a thorough understanding of why that’s important to you. You also probably don’t have time for a history/computer science course when all you want to do is convert that time stamp into something that looks nice for your boss in Excel. In that case, here you go:
All examples shown assume that the UNIX time stamp is in column A.
GMT – Greenwich Mean Time – This is the standard example you see across the Internet
=(A1/86400)+25569
EST – Eastern Standard Time (GMT – 5:00)
=((A1-18000)/86400)+25569
CST – Central Standard Time (GMT – 6:00)
=((A1-21600)/86400)+25569
MST – Mountain Standard Time (GMT – 7:00)
=((A1-25200)/86400)+25569
PST – Pacific Standard Time (GMT – 8:00)
=((A1-28800)/86400)+25569
Now set your column format to a Date field to your liking. I use the 3/14/01 13:30 format so I can include the easily sortable military time format.
9 Responses
How about for singapore
GMT +8:00 ??? I M in Dizzy in calculating.
Thanks and rgds
How about The DayLight Time?
Daylight savings time may be tricky. You’d need some logic in Excel to detect what the date is and then calculate if you need to alter the formula. I’ll try to look into that some more.
GMT + 8 would be:
=((A1-28800)/86400)-25569Actually, on my version of excel (mac 2008) the dates are off by 3 years. The correct formula seems to be
=(((A2+3600)/86400)+24107)
this is for GMT+1 of course
GMT -03:00.. help?
GMT -03:00 would be:
=((A1-10800)/86400)+25569
I’ve recently been working in importing datetimes from Excel into an SQL database using PHP. The library I was using to read the Excel sheet is called phpexcel. What I found was that there are just too many formats that a date can be stored in (dd/mm/yy, mm/dd/yy, yyyy-mm-dd) for strtotime to be able to handle. This lead me to import the Excel datetime values and convert to Unix values using the following function:
function time_format($datetime,$tzoffset){
if(is_numeric($datetime)){
if(date(‘I’)){
$datetime = (-25569-($tzoffset+1)/24+$datetime)*24*60*60;
}else{
$datetime = (-25569-$tzoffset/24+$datetime)*24*60*60;
}
}
$datetime = date(‘Y-m-d H:i:s’,$datetime);
return $datetime;
}
The variable $tzoffset is the timezone offset.
The date(‘I’) part checks if we are in daylight savings time or not.
I know this isn’t entirely relevant but hopefully it will be of use to someone.
[...] Are you using Excel for MAC 2011 and find your year is off by 3? You actually need to change the hack digits at the end of your formula from 25569 to 24107. Check out this link. [...]