Tuesday, March 17, 2009

converting unix date to excel date

been bugged by this for while - on unix the date timestamp is number of seconds since 1/1/1970
On excel, it's 1/1/1900
So to convert the unix timestamp to microsoft excel, use the formula:
=timestamp/86400 + "1/1/1970"
(where 86400 = 24 * 60* 60 ie: # seconds in a day).
Oh you might also want to add/subtract an offset for the timezone (depending on the timezone settings of your unix box). eg: for +10 GMT (sydney, melbourne, canberra) add 10/24 ie: 0.416667

ps: make the cell format Date or Time or Custom format.
Personally I prefer ddd dd/mm/yyyy HH:mm:ss (ie: Tue 03/03/1999 23:59:43)

No comments: