up vote 2 down vote favorite
share [g+] share [fb]

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?

link|improve this question
feedback

1 Answer

up vote 6 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.

link|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 Rory or Isznti Mar 3 '11 at 2:26
feedback

Your Answer

 
or
required, but never shown
discard

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