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?

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.

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

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

=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!!!

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: <strong>TimeZome</strong> where you put your TimeZone (ex: -3) and <strong>Factor</strong> 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