8

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?

1 Answer 1

15

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.

3
  • 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. Mar 2, 2011 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, 2011 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) Feb 13, 2013 at 10:10

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .