## Converting Unix Timestamps in Excel (Also use in Daqfactory, since 01/01/1970)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): `1217888348` 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: =(((A1/60)/60)/24) Then, you need to add the result to the date value for January 1, 1970. The modified formula is: =(((A1/60)/60)/24)+DATE(1970,1,1) 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: =(((A1/60)/60)/24)+DATE(1970,1,1)+(-5/24) A simpler (but much less clear) formula that returns the same result is: =(A1/86400)+25569+(-5/24) 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.
This function will interpret decimal seconds, so: StrToTime("10/22/06 3:12:05. |

R&D Publishing >