Convert EPOCH Time to Date/Time in Excel

April 7, 2011 4 Comments

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!

Filed under Microsoft

About Tim Smeltzer
I am a husband, father, and technologist. While I am very much interested in almost all technology, my current area of specialty is secure mobile messaging. You will find me blogging from time to time on mobile technology - what I think is cool, what I think is not cool, and how to do things. Please be nice if you leave me comments. I am really trying to help!

4 Responses to Convert EPOCH Time to Date/Time in Excel

  1. Jerry Anderson says:
    November 28, 2012 at 4:03 pm

    In Excel the numeric value received from Android SMS date is produced in milliseconds. This value must be converted to an Excel date. To do this, use the formula below:

    =SUM([@date]/60000/60/24,DATEVALUE(“1/1/1970”))

    Where:
    @date=Android SMS Date value
    60000=Milliseconds per Minute
    60=Minutes per Hour
    24=Hours per Day
    Datevalue(“1/1/1970)=Excel numeric date value for EPOCH date 1/1/1970

    This formula yealds a True Excel numeric date. Using the Format Cell+Date option will give you the readable date and time e.g. 4/7/11 12:50

    Reply
  2. Chris C. says:
    September 3, 2013 at 2:18 pm

    Hey Tim,

    AWESOME article – I was spinning my tires because my 2006 date kept coming back as 1936; thank you for the explanation on the use of EPOCH time. I am bookmarking this page right now.

    Reply

  3. Pingback: Convertir l’Epoch Unix en Heure légale | ZERFOS.SYSTEMS

Leave a reply to Tim Smeltzer Cancel reply