View source for How do I convert time stamps in my data (recorded in Unix time) to readable dates and times?

Time is recorded by LifeGuide in Unix time. Unix time is defined as '''the number of seconds that have elapsed since 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970, not counting leap seconds.'''

Below are two methods to convert Unix time to a readable date and time.

For converting one or two times:
Use a unix time converter (e.g. http://www.onlineconversion.com/unix_time.htm). Just copy and paste the number of seconds and click submit and this will give you a date stamp and exact time.
 
OR

For converting columns of data in Excel:
Use a excel formula - and drag it down for all cells. The formula is: =(((C3/60)/60)/24)+DATE(1970,1,1) This will give you the date or time (may be out by an hour as it's BST and it hasn't been off set for GMT). To get it to show date, you'll need to format the cell to show "date". To get it to show time you'll need to format the cell to show "time" (right-click and select '''Format Cells''', then select '''Date''' or '''Time'''.  See screenshots below for the columns you will need in Excel and the formula.


[[Image:UnixtimeExcelFormulaScreenshot1.png]]


[[Image:UnixtimeExcelFormulaScreenshot2.png]]

[[Category:Excel]]
[[Category:Formula]]
[[Category:Time]]