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?



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.

| improve this answer | |
  • 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 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 d C e Freitas Feb 13 '13 at 10:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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