Converting Unix Time into Human Readable Format in Excel

7

2

I have a spreadsheet containing several timestamp columns, given in Unix time, i.e. number of seconds since 01/01/1970 00:00.00 UTC.

Using Excel 2010, are there any methods, and if so how, to convert these timestamps into a date/time format recognizable by Excel?

Scott Pack

Posted 2011-03-02T14:45:46.643

Reputation: 1 048

Answers

14

Try:

value / 86400 + "1/1/1970"

Division by 86400 is needed to convert it into days (product of 24 * 60 * 60), you then add the number of days between 1st Jan 1900 (which is where Excel bases its dates off) and 1st of Jan 1970.

cbz

Posted 2011-03-02T14:45:46.643

Reputation: 741

2+1: I prefer (timestamp / 86400 + 25569) but they're the same thing. Format the field as date/time and you're good to go. – Satanicpuppy – 2011-03-02T15:06:21.073

& @Satanicpuppy: +1 - Confirmed both methods work and produce identical results using the Unix Time entry at Wikipedia. Properly implemented, the formulas should translate 1299098490 to 03/02/2011 @ 20:41:30 Zulu after formatting the cell to date/time. – Iszi – 2011-03-03T02:26:02.473

Only works if your locale is set to "DD/MM/YYYY", whereas other people have it as "YYYY/MM/DD", then you have to use the localized date format. Rather use DATE(1970,1,1) – David d C e Freitas – 2013-02-13T10:10:58.047

Asked: 2011-03-02T14:45:46.643

Viewed: 19 348 times

Active: 2019-07-02T23:43:31.867