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 :)

Simply Great!

Thanks a ton.
It worked.

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")

Hi,

I have a UTF-8 database extract where the date appears like this: 1.44003E+12. I need to convert this value to a date in Excel. I tried using =CELL/(60*60*24)+"1/1/1970", (where my cell is A1), but I end up with this: 16700780.07. Please help! I have scoured the web and cannot find a solution that works. I am working on a Mac. Thank you!!!

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

Sometimes the value coming out is not seconds, but milliseconds.

Hello ,
one questions everyone asks, is about how to Bulk Convert when you have Epoch time in an Excel sheet with thousands of Rows to convert.
following few steps would do the magic

For Example ,
I have a CDR downloaded from Cisco Call Manager . The Call originating time usually comes up on Column E
I want to read it in human readable format and I have 1000 Rows to convert.
If there is no other referencing or calculation running on the excel sheet, I can insert a new column after E , by right clicking on Letter F and selecting insert Column (But if there is referencing already running in complex excel sheet, I would add a new sheet as inserting column can mess up other formulas so please be sure)

Column F is now Blank Column, I would call it Readable Call Originating Time , this description would be on F1
actual data starts from E2 to E1000 that I need to convert and place it in F2 to F1000

1. use the provided formula =(((E:E/60)/60)/24)+DATE(1970,1,1)
Where E:E is the Cell number of Excel cell , if I were to convert only one cell E1 to F1 , I would just put E1 but since I have 1000 to convert ,
I would use E:E (which plainly means what ever is the row on E , I am on same Row). if your Column is A and you want all of it converted and placed in B, then you would use A:A in the formula placed in cells B2 to B1000.

2. Copy the same formula to all the F Column cells from F2 to F1000 (this can be done by one left click on cell F2 , you will see F2 would have highlighted border and on the right bottom corner of cell you will see a square dot, left click on the right bottom corner dot of the cell and drag it down , once you reach 1000 release the left click)

3. Change the Column format by right clicking on F Column and selecting Format Cells (you will see the whole Column is selected) and select Custom and select dd/mm/yyyy hh:mm or what ever format you prefer.

you could do all in one go as well by adding all the cell formatting within the formula as suggested above , its your choice , once you get understanding of each step , you can do step three as step 1 by changing the format prior to filling data.

Hope this helps everyone
Anees

=CELL/(60*60*24*1000)+"1/1/1970"
This update worked for me brilliantly....

Been going back and forth with this trying to get the correct formula for CST. Is this correct:

Convert the number 1449242079 using this formula - =CELL/(60*60*24)+"12/31/1969 6:00:00 pm"
I get 12/4/15 9:14 AM (representing a Dec. 4, 2015 date.) Just not sure about the exact time for CST.

Thoughts?

To display using your correct TimeZone, create 2 new cells: TimeZome where you put your TimeZone (ex: -3) and Factor where you put the formula =TimeZone*60*60. Change de formula by =(CELL+Factor)/(60*60*24)+DATE(1970,1,1). This worked for me.!!!

Hi. I have a downloaded date of 10 numbers that start with 3899 that I need to convert to date and time. Hope someone can help. I'm reasonably good on Excel and have spent time trying to track this down but only find numbers starting at etc which are the UNIX ones. I have to manually plough through pages of clocking in data to extract minutes and hours on certain days to pay workers so am looking to crack this to save me a lot of time. Help?

I had to fix unixdate formula to make it work worldwide.

UnixStartDate = (DateSerial(StartDateYr, StartDateMo, StartDateDay) - #1/1/1970#) * 86400
UnixEndDate = (DateSerial(EndDateYr, EndDateMo, EndDateDay) - #1/1/1970#) * 86400

Hi,
I've tried these formulas and formatting for date/time (several of them). I get all # when I try to format.
Any idea about what I can do to resolve?
Lisa

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.