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"

Why

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

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?

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'

Hi Thomas,

Google for Excel how to copy formula

Hey, thanks for the tip

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

Does it matter which regular date format is used?

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

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

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

Well done

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

Keep it up

Re: Excel - Convert Unix Time to Excel Time

Thank You Vadim. the formula works just great.

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

Which regular date format to use?

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

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

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

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/

Good post

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

Still useful :)

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

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

great post, thanks for share.

great post, thanks for share.

Convert date and time to UNIX format

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 .

UNIX to Excel with TIMEZONE

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.

Returns a number

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

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.

Format the cell as Date.

For 16-digit dates I had to do this

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

Post new comment

The content of this field is kept private and will not be shown publicly.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <java>, <powershell>, <tsql>, <visualfoxpro>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.
  • Web page addresses and e-mail addresses turn into links automatically.

More information about formatting options

By submitting this form, you accept the Mollom privacy policy.