Converting Unix Timestamps

Category: Formulas | [Item URL]

If you import data you might encounter time values stored as Unix timestamps. Unix time is defined as the number of seconds since midnight (GMT time) on January 1, 1970 -- also known as the Unix epoch.

For example, here's the Unix timestamp for August 4, 2008 at 10:19:08 pm (GMT):


To create an Excel formula to convert a Unix timestamp to a readable data and time, start by converting the seconds to days. This formula assumes that the Unix timestamp is in cell A1:


Then, you need to add the result to the date value for January 1, 1970. The modified formula is:


Finally, you need to adjust the formula for the GMT offset. For example, if you're in New York the GMT offset is -5. Therefore, the final formula is:


A simpler (but much less clear) formula that returns the same result is:


Both of these formulas return a date/time serial number, so you need to apply a number format to make it readable as a date and time.

Search for Tips

All Tips

Browse Tips by Category

Tip Books

Needs tips? Here are two books, with nothing but tips:

Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers

Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks

© Copyright 2016, J-Walk & Associates, Inc.
Privacy Policy