convertingunixtimestamps
no way to compare when less than two revisions
Differences
This shows you the differences between two versions of the page.
— | convertingunixtimestamps [2013/01/28 04:29] (current) – created - external edit 127.0.0.1 | ||
---|---|---|---|
Line 1: | Line 1: | ||
+ | ====== Converting 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: | ||
+ | < | ||
+ | '' | ||
+ | </ | ||
+ | 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. | ||
+ | |||
+ | -- Main.FredPettis - 2011-02-15 |
convertingunixtimestamps.txt · Last modified: 2013/01/28 04:29 by 127.0.0.1