Excel - Convert Unix Time to Excel Time

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"

Comments

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

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

where CELL is the cell containing a regular date:

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

Hi Thomas,

Google for Excel how to copy formula

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

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 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.

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

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

Keep it up

Thank You Vadim. the formula works just great.

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

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

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 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 that shows you how to convert your excel to web in minutes http://blog.caspio.com/integration/convert-ms-excel-to-web/

I found this useful about a year ago and it has helped me again today.
Thanks!

Alright, I'd like to bring my little stone to this matter :

=TEXT(A1/(60*60*24)+"1/1/1970","yyyy/mm/dd hh:mm:ss")

"A1" being any cell.

This formula is a little bit refined : you got the date format in the same time :)

Thanks for your assistance with this formula - it was very helpful :)

great post, thanks for share.

HI,

I have to convert date (yyyy/mm/dd) with respective time of the day (hh/mm/ss) to UNIX time stamp in excel.
Please help me .

You may add timezone to mix by change the 1/1/1970 date and adding time to it.

For instance for US Eastern time (NY) change the date to "12/29/1969 8:00:00 pm" or CELL/(60*60*24)+"12/29/1969 8:00:00 pm"

EST is -5 and because of daytime saving on spring it will be -4 so the timestamp is 4 hours before 1/1/1997. After daytime saving it will be -5 again.

On other time zone like +6 you may use "1/1/197 6:00:00 am"

This worked for me.

The formula =CELL/(60*60*24)+"1/1/1970" returns a number. Any idea how to convert that number to DateTime. For example while converting the value 1403877600000 using the above formula it returns 16274152.33

Try DATE(1970,1,1) instead of "1/1/1970". Also in case you're not aware of it: the 13 digit unix timestamp contains extra precision that you'll either need to drop or handle in some other way.

Format the cell as Date.

=TEXT((A1/1000000)/(60*60*24)+DATE(1970,1,1),"mm/dd/yyyy hh:mm:ss")

Add new comment

Filtered HTML

  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.
  • Web page addresses and e-mail addresses turn into links automatically.

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
By submitting this form, you accept the Mollom privacy policy.