Convert EPOCH Time to Date/Time in Excel

And now for something completely different. I was recently asked to retrieve SMS messages from an old iPhone. I was able to get to the backup of the SMS database easily enough, and it opened with SQL Lite, and I exported the messages to Excel. When I started looking at the messages in Excel, I noticed that the “date” field was a string of numbers that looked something like 1302180658.

After looking into this, I discovered that this was the Epoch Time (aka the UNIX Time). This is the number of seconds since January 1, 1970 00:00:00 UTC. I then spent a little time using Google to find out how to convert this to a real Date/Time. I found plenty of online tools to do so, and I even found a site that had a formula for Excel. When I used the formula from the web site, I got a date in the future, so I knew something was wrong.

I then broke down the formula into its components and discovered that the constant that was provided in the formula I got from the web site was wrong. Anyhow, enough back story. Here is how to convert Epoch Time (UTC) to Date/Time (UTC) in Excel:

Row A contains the Epoch Time (i.e., 1302180658)
Cell B2 contains the date January 1, 1970 (in a date formatted cell that is named - I called mine EPOCH)  This is a constant.
Row C contains the following formula: =sum(A2/86400)+EPOCH

Here is a visual

Hope this helps – please let me know if you have questions or issues with using this.  Thanks for stopping by!

Advertisement