Преобразование метки времени Unix в дату Excel
Чтобы преобразовать метку времени Unix в формат даты Excel, вы можете использовать формулу, основанную на функции ДАТА. В показанном примере формула C5 имеет следующий вид:
Объяснение
Метка времени Unix отслеживает время как текущий счетчик секунд. Отсчет начинается в «эпоху Unix» 1 января 1970 года, поэтому метка времени Unix — это просто общее количество секунд между любой заданной датой и эпохой Unix. Поскольку день содержит 86400 секунд (24 часа x 60 минут x 60 секунд), преобразование во время Excel может быть выполнено путем деления дней на 86400 и добавления значения даты 1 января 1970 года.
В показанном примере формула сначала делит значение отметки времени в B5 на 86400, а затем добавляет значение даты для эпохи Unix, 1 января 1970 г. Формула вычисляется следующим образом:
Когда C5 отформатирован с датой Excel «д-ммм-гггг», дата отображается как 1 октября 2018 г.
Как Excel отслеживает даты и время
Система дат Excel начинается 1 января 1900 года и ведет отсчет вперед. В таблице ниже показаны числовые значения, связанные с несколькими случайными датами:
| Дата | Исходное значение |
|---|---|
| 1 января 1900 г. | 1 |
| 28 июля 1914 г. 00:00 | 5323 |
| 1 января 1970 г., 00:00 | 25569 |
| 31 декабря 1999 г. | 36525 |
| 1 октября 2018 г. | 43374 |
| 1 октября 2018 г. 12:00 | 43374,5 |
Обратите внимание, что последняя дата также включает время. Поскольку один день равен 1, а один день равен 24 часам, время в Excel можно представить в виде дробных значений от 1, как показано в таблице ниже. Чтобы увидеть значение, отображаемое как время, необходимо применить формат времени.
Convert Excel Time to Unix Time in Excel & Google Sheets
Unix time is also known as Epoch Time or POSIX time or Unix timestamp. It’s a system that counts the number of seconds that have elapsed since the Unix Epoch, i.e. January 1 st , 1970. To put in simple words, Unix time is the total number of seconds between a date and Unix Epoch.
Convert Excel Time to Unix Time
To calculate Unix time, first, we need to find the total number of days between the Excel time and Epoch time. Then multiply the calculated days by 86,400, because a day has 86,400 seconds (24 hours × 60 minutes ×60 seconds = 86,400 seconds).
Step by Step Explanation
The first step in the conversion of Excel time to Unix time is to calculate the numeric value of the Epoch date. This can be done by using the DATE function.
It gives us this value:
In the same way, the calculation of the numeric value of Epoch date is subtracted from the given Excel time’s numeric value
After that, the difference calculated from above is multiplied by 86400 to get the resultant value in seconds
This is the total number of seconds, we get, between two dates
Convert Unix Time to Excel Time
Inversely, we can convert the Unix time to Excel time with the help of the following formula:
Step By Step Explanation
First, the Unix timestamp is divided by the total number of seconds in a day, i.e. 86400.
Now, we calculate the numeric value of the Epoch date through DATE function
Once we have the numeric value of the Epoch date, we’ll add these two values
= 14853 + 25569
= 40422
The resultant value, we got after the calculation, is in the serial date number format. To view it in date format, simply change the format of the cell to Date or your required custom format from the Number Format by accessing it through the Home Tab
or by pressing CTRL + 1
After changing the format of the numeric values of the Excel date & time, we get the following Excel date and time
Convert Excel Time to Unix Time in Google Sheets
The conversion formula for time to Unix time works exactly the same in Google Sheets as in Excel.
Как преобразовать метку времени эпохи Unix в удобочитаемую дату / время в Excel?
У меня есть документы Excel, содержащие временные метки эпохи Unix из приложения Java. Я хотел бы видеть, что они переводили и представляли их как удобные для чтения даты внутри Excel.
Например, следующий длинный: 1362161251894 должен оценивать что-то читаемое: 01 марта 2013 г. 11: 07: 31,894
Я предполагаю, что могу создать формулу для этого, но я не уверен, как это сделать. Спасибо!
3 ответа
Да, вы можете создать формулу для этого. Java и Unix/Linux подсчитывают количество миллисекунд с 1 января 1970 года, в то время как Microsoft Excel запускает это с 1 января 1900 года для Windows и 1/1/1904 для Mac OS X. Вам просто нужно сделать следующее: конвертировать:
Время по Гринвичу в Windows
Время по Гринвичу в Mac OS X
Для локального времени в Windows (замените t на текущее смещение от GMT)
Для локального времени в Mac OS X (замените t текущим смещением от GMT)
В вашем конкретном случае похоже, что вы находитесь в горном времени (смещение по часовой стрелке 7). Поэтому, если я вставляю ваше значение в 1362161251894 в новую таблицу Excel в ячейке A1, а затем вставляю следующую формулу, я получаю результат 41333.46356, который, если я затем скажу Excel в формате Date ( нажмите ctrl + 1 на ячейке): 2/28/13 11:07 AM
Excel выражает дни как целые значения и время как дробные значения. Поэтому, если время эпохи было предоставлено в миллисекундах начиная с 1-1-1970 из UNIX, мы хотим разделить на количество миллисекунд в год и добавить Excel в 1-1-1970, чтобы обеспечить читаемое время UTC для человека. Если ваше значение находится в ячейке A1, то Excel потребуется:
Заметьте, что вы можете сбросить /1000, если время вашей эпохи было в секундах, а не в миллисекундах. Чтобы преобразовать в локальное время, где ‘t’ — ваше местное смещение UTC (не забудьте использовать отрицательное значение, если у вас есть отрицательное смещение UTC), вы можете добавить/вычесть смещение UTC:
Обратите внимание, что ваше смещение по UTC может варьироваться в зависимости от того, наблюдается ли летнее время в вашем местоположении, что делает это неполным решением для отображения локальных мест в течение года в одной электронной таблице.
Recent posts
If you ever find yourself needing to convert from UNIX time in Excel, here’s the solution.
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
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
to see the current unix time.
Comments
It's easy
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.
Google for 'Excel how to copy formula'
Google for <b>Excel how to copy formula</b>
Hey, thanks for the tip
Does it matter which regular date format is used?
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?
An excellent example
Well done
Hey thanks for the formula man.What a great site.Bravoo.
Re: Excel — Convert Unix Time to Excel Time
Thank You Vadim. the formula works just great.
Оценка: 6 по 5 и бальной шкале
Which regular date format to use?
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
Great post. Here’s a post
Good post
Still useful 🙂
Alright, I’d like to bring my little stone to this matter :
«A1» being any cell.
This formula is a little bit refined : you got the date format in the same time 🙂
Thanks for your assistance
Kudos to you!
Thanks
great post, thanks for share.
Convert date and time to UNIX format
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
Format the cell as Date.
For 16-digit dates I had to do this
converting UTF-8 to date in Excel
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.
Try this instead:
Sometimes the value coming out is not seconds, but milliseconds.
Epoch time Bulk Conversion in Excel
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.