Excel - Convert Unix Time to Excel Time

Submitted by Vadim on December 2, 2007 - 01: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: 4.3 (26 votes)

Why

Submitted by Anonymous (not verified) on April 3, 2009 - 07: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 - 15: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 - 14: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 - 12:46.

Hi Thomas,

Google for Excel how to copy formula

Hey, thanks for the tip

Submitted by Alan Almonte (not verified) on December 4, 2011 - 11:01.

Hey, thanks for the tip. That did the job ; )

Does it matter which regular date format is used?

Submitted by Anonymous (not verified) on June 12, 2010 - 15:59.

What format should be used for regular date format as there are several used? So, I just the regular date format into formula and it will convert to Unix time? Would it look like this? =(B1-"1/1/1970")*60*60*24

Of course you have the right

Submitted by Olaf (not verified) on May 30, 2009 - 04: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 - 05:33.

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

Well done

Submitted by vedic mathematic (not verified) on March 26, 2010 - 01:47.

Hey thanks for the formula man.What a great site.Bravoo...

Keep it up

Re: Excel - Convert Unix Time to Excel Time

Submitted by Anonymous (not verified) on April 14, 2010 - 06:46.

Thank You Vadim. the formula works just great.

Оценка: 6 по 5 и бальной шкале

Which regular date format to use?

Submitted by Anonymous (not verified) on June 12, 2010 - 16:03.

What format should be used for regular date format as there are several used? So, I just the regular date format into formula and it will convert to Unix time? Would it look like this? =(B1-"1/1/1970")*60*60*24

Does not take the time zone into account

Submitted by Anonymous (not verified) on November 24, 2011 - 16:15.

Be aware that Unix time is the number of seconds since January 1, 1970 for UTC/GMT while Excel uses the number of days and fractions of the day for the local time zone.

If you convert a value such as 1322164881 using =CELL/(60*60*24)+"1/1/1970" you will get 2011-11-24 20:01:21 which is correct for UTC or the GMT time zone.

I'm in Pacific standard time time at the moment and expected to see 2011-11-24 12:01:21. Unfortunately, discovering the current time zone offset in Excel is a major pain. I found this page, http://www.cpearson.com/excel/TimeZoneAndDaylightTime.aspx. The LocalOffsetFromGMT() function on that page works and returns the number of minutes from GMT meaning I divide that by 60 and get the fraction of the day Excel uses.

Even that is not perfect. For example, let's say I have 1314627183 which translates to 2011-08-29 14:13:03 GMT. We were on daylight savings at the time time. My current GMT offset is 8 hours but during the summer it's 7 hours and so 1314627183 should translate to 2011-08-29 07:13:03 for me. To add to the pain, a few years ago the USA changed the dates we switched to/from daylight savings time. In my case, I decided I did not care if a displayed time was off by an hour should the daylight savings mode be different between "now" and a given Unix time and so using "LocalOffsetFromGMT()/8" works for me.

Someone asked above "What format should be used for regular date format as there are several used?" You can use "1970-01-01" which Excel should translate reliably. If that fails then use =CELL/(60*60*24)+DATE(1970,1,1). As it is, it won't matter if your local format is "d/m/yyyy" as "1/1/1970" is the same regardless of the month/day order.

Thanks for the info. For the

Submitted by Anonymous (not verified) on January 12, 2012 - 21:43.

Thanks for the info. For the formatting in excel, if you want to see the date and time, use a custom cell format = m/d/yyyy h:mm:ss

Great post. Here’s a post

Submitted by Cameron Reid (not verified) on January 19, 2012 - 12:20.

Great post. Here’s a post that shows you how to convert your excel to web in minutes http://blog.caspio.com/integration/convert-ms-excel-to-web/