User Tools

Site Tools


convertingunixtimestamps

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

convertingunixtimestamps [2013/01/27 21:29] (current)
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):
 +<​file>​
 +1217888348
 +</​file>​
 +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:
 +<​file>​
 +''​(((A1/​60)/​60)/​24)
 +</​file>​
 +Then, you need to add the result to the date value for January 1, 1970. The modified formula is:
 +<​file>​
 +''​(((A1/​60)/​60)/​24)+DATE(1970,​1,​1)
 +</​file>​
 +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:
 +<​file>​
 +''​(((A1/​60)/​60)/​24)+DATE(1970,​1,​1)+(-5/​24)
 +</​file>​
 +A simpler (but much less clear) formula that returns the same result is:
 +<​file>​
 +''​(A1/​86400)+25569+(-5/​24)
 +</​file>​
 +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/27 21:29 (external edit)