How do I convert time stamps in my data to readable dates and times?

From Lifeguide Wiki
Jump to navigation Jump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

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). Then 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