Excel - Convert Unix Time to Excel Time

Submitted by Vadim on December 2, 2007 - 00:00

If you ever find yourself needing to convert from UNIX time in Excel, here's the solution.

Explanation/Methodology

Unix time is the number of seconds since January 1, 1970.

Excel doesn't contain built-in functions for working with Unix dates so they must be derived.
Excel allows you to add a number of days to a date by using the "+" operator. Let's make use of that.
First convert the number of seconds to number of days (by dividing by 60*60*24) and then add the result to the date "1/1/1970".
The formula will look like

=CELL/(60*60*24)+"1/1/1970"

The quotes around the date are required. If they are not present, Excel will treat 1/1/1970 as an expression.

On Linux (or any other OS that uses coreutils), you can type

date +%s

to see the current unix time.

Summary

=CELL/(60*60*24)+"1/1/1970"

Your rating: None Average: 5 (3 votes)

Why

Submitted by Anonymous (not verified) on April 3, 2009 - 06:48.

Why are there several hundred examples on the web of converting unix time to excel time - and not a SINGLE one to convert the other way?

It's easy

Submitted by Anonymous (not verified) on May 18, 2009 - 14:52.

It's easy

=(CELL-"1/1/1970")*60*60*24

where CELL is the cell containing a regular date:

Can I calculate a whole column of timestamps to unix time?

Submitted by Thomas Allan (not verified) on July 24, 2009 - 13:18.

Hey man. How do I convert a whole COLUMN of timestamps, to unix time in another column then? (a formula I place in the top of the column that calculates this whole column).

I have a huge XML file with thousands of timestamps, (listed in the format: "14-06-2009 16:21:01", "24-02-2007 19:25:10", etc.) that I want to convert to unix time.
Pasting a formula for EACH of these entries one at a time, would be death.

Regards, Thomas

Google for 'Excel how to copy formula'

Submitted by Sergey on July 28, 2009 - 11:46.

Hi Thomas,

Google for Excel how to copy formula

Of course you have the right

Submitted by Olaf (not verified) on May 30, 2009 - 03:41.

Of course you have the right to complain that you didn't pay attention in math lessons in school.

But what is so hard in transforming a simple math formular to the inverse computation?

Examples: an hour is 60 mintes, so minutes=60*hours. Now, how do you compute the hours, if you know the minutes?

Bye, Olaf.

An excellent example

Submitted by Mick Wood (not verified) on October 13, 2009 - 04:33.

Congratulations Vadim - this is the clearest example I have seen for this time conversion.
I rate this 5 out of 5 :-))