Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 3 down vote favorite

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?

share|improve this question

1 Answer

up vote 7 down vote accepted

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 it's dates off) and 1st of Jan 1970.

share|improve this answer
1  
+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 Mar 2 '11 at 15:06
& @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 Mar 3 '11 at 2:26
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 Freitas Feb 13 at 10:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.