12 Feb 2009

Excel Formula to convert Epoch time to localtime (IST)

UNIX time is the number of seconds that has elapsed since 1/1/1970 [DATE(1970,1,1) = 25569].

Excel calculates dates by using the number of days that has elapsed since 1/1/1900.

Therefore you should be able to convert from one to the other by converting from seconds to days, and then adding on the 70 odd years difference, Plus 5:30 hours for IST (330 mins = 19800 secs).

Thus The formula : =((A1+19800)/86400)+25569 where A1 contains the UNIX time should convert to Excel date/time.

Make sure you format the cell as the required date/time format.

BTW, if you wish to convert a unix time using a perl one liner:

perl -e 'print scalar localtime(x),"\n"'

where x is the unix time.

No comments:

Post a Comment