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

From Lifeguide Wiki
Revision as of 13:35, 23 March 2015 by Judy (talk | contribs)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

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.


UnixtimeExcelFormulaScreenshot1.png


UnixtimeExcelFormulaScreenshot2.png