Converting UNIX Timestamps to Excel Dates

calendar Oct 10, 2011 · 1 min read · microsoft excel  ·
Share on: linkedin copy

Faced with analysing a proprietary log file from a Linux system I turned to Excel to look at the data. The dates in this log file were stored in the standard UNIX timestamp format (number of seconds since midnight on the 1st January 1970) so I needed a formula to convert this into something readable. This is what I came up with.

With the timestamp value in cell A2, the formula below provides the value in the Excel Date/Time format, this target cell was formatted using the custom type dd/mm/yyyy hh:mm:ss

1=DATE(1970,1,1)+A2/86400

Remember these times are usually from the system clock, so may not be adjusted for daylight savings or time zone.