Covert UNIX Epoch dates in Microsoft Excel – Including Timezone Examples
Posted on January 22nd, 2009 | 5 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.
5 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