Как timestamp перевести в дату в excel
Как преобразовать дату и метку времени Unix в Excel?
Временная метка Unix также называется временем эпохи или временем POSIX, которое широко используется во многих операционных системах или форматах файлов. В этом руководстве обсуждается преобразование даты в метку времени Unix в Excel.
Преобразовать дату в метку времени
Просмотр и редактирование с вкладками нескольких книг Excel / документов Word, таких как Firefox, Chrome, Интернет 10!
Возможно, вы знакомы с просмотром нескольких веб-страниц в Firefox / Chrome / IE и можете переключаться между ними, легко щелкая соответствующую вкладку. Здесь вкладка Office поддерживает аналогичную обработку, позволяя просматривать несколько книг Excel или документов Word в одном окне Excel или Word и легко переключаться между ними, щелкая вкладку. ЧтобыНажмите на бесплатную 45-дневную пробную версию Office Tab!
Чтобы преобразовать дату в метку времени, вы можете использовать формулу для решения проблемы.
Выберите пустую ячейку и введите эту формулу = (A1-DATE (1970,1,1)) * 86400 Enter и нажмитевойтиKey, при необходимости, вы можете применить диапазон этой формулы, перетащив маркер автозаполнения. Теперь ряд ячеек даты преобразован в отметки времени Unix.
Преобразование даты и времени в метку времени
Существует формула, которая поможет вам преобразовать дату и время в метку времени Unix.
1. Во-первых, вам нужно ввести всемирное координированное время в ячейку 01.01.1970. Смотрите скриншот:
2. Затем введите эту формулу = (A1- $ C $ 1) * 86400 в ячейку, нажмитеВойтиKey и при необходимости используйте эту формулу, чтобы перетащить дескриптор автозаполнения в диапазон. Смотрите скриншот:
подсказки: В формуле A1 - это ячейка даты и времени, а C1 - введенная вами координата мирового времени.
Преобразовать метку времени на дату
Если у вас есть список меток времени, которые необходимо преобразовать в даты, вы можете выполнить следующие действия:
1. В пустой ячейке рядом со списком отметок времени введите эту формулу = (((A1 / 60) / 60) / 24) + ДАТА (1970,1,1), нажмитевойтиButton, а затем перетащите маркер автозаполнения в нужный диапазон.
2. Затем щелкните правой кнопкой мыши ячейку, в которой используется формула, и выберитеФормат ячейкиИз контекстного меню, а затем во всплывающемФормат ячейкиДиалоговое окно под NУмбраЯрлык, щелкнитеДатаВкатегорияСписок, а затем выберите тип даты справа.
3. НажмитеOK, Теперь вы можете видеть, что метка времени Unix была преобразована в дату.
незамедлительный:
1. A1 представляет нужную ячейку с отметкой времени.
2. Эту формулу также можно использовать для преобразования серии отметок времени в дату и время, просто отформатируйте результат в формате даты и времени.
Быстро и легко конвертируйте даты в другие форматы дат в Excel
Если вы хотите преобразовать ячейку формата даты и времени только в значение даты, такое как 2016/4/7 1:01 AM до 2016/4/7, эта статья может вам помочь.
One click to convert mm.dd.yyyy or other date format to mm/dd/yyyy format in Excel:
Click Kutools > Content > Convert to Date. The Kutools for Excel's Convert to Date utility helps you easily convert mm.dd.yyyy format (such as 01.12.2015 ) or Thursday, September 11, 2014 or other special date format to dd/mm/yyyy format in Excel. See below screenshot: Download the full feature 60-day free trail of Kutools for Excel now!
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download the free trial Now!
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools : Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools : Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color ; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment.
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
Преобразование ячейки формата даты / времени в дату только с помощью формулы
Следующая формула поможет вам преобразовать ячейку формата даты / времени в дату только в Excel.
1. Выберите пустую ячейку, в которую вы поместите значение даты, затем введите формулу. = МЕСЯЦ (A2) & "/" & ДЕНЬ (A2) & "/" & ГОД (A2) в строку формул и нажмите Enter ключ.
2. Вы можете видеть, что только значение даты заполняется в выбранной ячейке на основе ячейки даты / времени. Продолжайте выбирать ячейку даты, затем перетащите маркер заполнения вниз, чтобы отобразить все даты, основанные на других ячейках даты / времени. Смотрите скриншот:
Легко удаляйте все времена из дат всего несколькими щелчками мыши
В этом разделе показано, как легко удалить все времена из выбранных ячеек даты с помощью утилиты Удалить время из даты в Kutools for Excel. Пожалуйста, сделайте следующее.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Выберите пустую ячейку для определения даты после удаления времени из (говорит C2). Нажмите Кутулс > Формула Помощник > Формула Помощник. Смотрите скриншот:
2. в Помощник по формулам диалоговое окно, настройте следующим образом.
- В Выберите формулу коробка, найдите и выберите Удалить время из даты;
Советы: Вы можете проверить Фильтр введите определенное слово в текстовое поле, чтобы быстро отфильтровать формулу. - Выберите ячейку с датой и временем, вам нужно сохранить только дату в DateTime коробка;
- Нажмите OK кнопку.
3. Продолжайте выбирать первую ячейку результата, затем перетащите маркер заполнения вниз, чтобы получить все результаты, как вам нужно, как показано ниже:
Теперь все времена удалены из указанных дат.
Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Преобразование ячейки формата даты / времени в дату только с помощью Kutools for Excel
Вот рекомендую удобный инструмент - Kutools for Excel, С этими Применить форматирование даты Утилита, вы можете легко преобразовать ячейку формата даты / времени в любой формат даты, который вам нужен.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Выберите ячейку формата даты / времени, в которой будет отображаться только значение даты, затем щелкните Кутулс > Формат > Применить форматирование даты. Смотрите скриншот:
2. в Применить форматирование даты диалоговом окне выберите формат даты в Форматирование даты поле, а затем щелкните OK кнопку.
Теперь ячейки формата даты / времени отображаются только как значение даты. Смотрите скриншот:
Внимание: Хотя ячейки формата даты / времени отображаются только как дата, отображаемые значения в строке формул по-прежнему имеют формат даты / времени. Если вы хотите преобразовать значения, отображаемые в строке формул, в их фактические значения, выберите их и щелкните Кутулс > К фактическому. Смотрите скриншот:
Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Office Tab - Tabbed Browsing, Editing, and Managing of Workbooks in Excel:
Office Tab brings the tabbed interface as seen in web browsers such as Google Chrome, Internet Explorer new versions and Firefox to Microsoft Excel. It will be a time-saving tool and irreplaceble in your work. See below demo:
В этом эссе рассказывается о формулах, которые преобразуют время Excel в метку времени Unix на листе Excel.
Прочтите перед формулой:
Что такое временная метка Unix?
Метка времени Unix записывает время в секундах. В системе Unix он отсчитывает время с 1 января 1970 года. Другими словами, временная метка Unix - это общее количество секунд между данной датой и 1 января 1970 года.
Если вы хотите следовать этому руководству, загрузите пример электронной таблицы.
Преобразование времени Excel в метку времени Unix
Общая формула:
(Excel_time-DATE(1970,1,1))*86400 |
Синтаксис и аргументы
Excel_time: the datetime that you want to convert to Unix time. |
Возвращаемое значение
замечание
Если заданное время Excel больше или равно 1 января 1900 г., но меньше 1 января 1970 г., возвращается отрицательное число; если время Excel равно 1 января 1970 г., возвращается ноль; если время в Excel больше 1 января 1970 г., возвращается положительное число.
Ошибка
Как работает эта формула
Здесь, чтобы преобразовать время Excel в ячейке B3: B6 в Unix Time, используйте следующую формулу:
Нажмите Enter Чтобы получить первый результат, перетащите дескриптор автозаполнения в ячейку C7.
объяснение
Функция даты: создать дату с номером года, месяца и дня.
Формула (B3-DATE (1970,1,1)) используется для вычисления разницы между заданной датой и временем эпохи Unix.
86400: Как известно, один день = 24 часа, один час = 60 минут и одна минута = 60 секунд, поэтому один день = 24 * 60 * 60 = 86400 секунд.
Преобразование метки времени Unix в время Excel
Общая формула:
(Unix_time/86400)+DATE(1970,1,1) |
Синтаксис и аргументы
Unix_time: the Unix time (a series number) that you want to convert to standard Excel time. |
Возвращаемое значение
Как работает эта формула
Чтобы преобразовать время Unix в ячейке B3: B6 в время Excel, используйте следующую формулу:
Нажмите Enter и перетащите дескриптор автозаполнения в ячейку C6.
Затем оставьте выбранными результаты и нажмите Ctrl + 1 для отображения Формат ячеек диалога под Число вкладка, выберите Время из Категория список, затем в правом разделе Тип, выберите нужный вам формат.
Нажмите OK, после чего результаты формулы будут отформатированы как datetime.
объяснение
Функция даты: создать дату с номером года, месяца и дня.
Формула (B3 / 86400) + DATE (1970,1,1) возвращает общее количество дней между заданной датой и эпохой Unix.
86400: Как известно, один день = 24 часа, один час = 60 минут, одна минута = 60 секунд, поэтому один день = 24 * 60 * 60 = 86400 секунд. И наоборот, дата / 86400 получит номер дня.
Относительные формулы
В этом руководстве представлена формула для преобразования десятичных минут во время Excel.
Чтобы преобразовать десятичные секунды во время Excel в Excel, вам может помочь формула.
Здесь представлена формула, которая преобразует время в десятичные часы в Excel и объясняет, как работает формула.
В этом руководстве представлена формула для преобразования времени в десятичные минуты в Excel.
Относительные функции
Тест на указанные условия, затем возвращает соответствующие значения
Преобразуйте текст в число.
МЕСЯЦ используется для получения месяца в виде целого числа (от 1 до 12) от даты.
Функция ДЕНЬ получает день в виде числа (от 1 до 31) от даты
Функция ГОД возвращает год на основе заданной даты в формате 4-значного серийного номера.
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и т. Д.) И экономия 80% времени для вас.
I have Excel documents containing Unix epoch timestamps from a Java application. I'd like to see what they translate to and represent them as human readable dates inside of Excel.
For example, the following long: 1362161251894 should evaluate to something readable like: 01 Mar 2013 11:07:31,894
I'm assuming I can create a formula for this, but I'm not sure how. Thanks!
6 Answers 6
Yes, you can create a formula to do this for you. Java and Unix/Linux count the number of milliseconds since 1/1/1970 while Microsoft Excel does it starting on 1/1/1900 for Windows and 1/1/1904 for Mac OS X. You would just need to do the following to convert:
For GMT time on Windows
For GMT time on Mac OS X
For local time on Windows (replace t with your current offset from GMT)
For local time on Mac OS X (replace t with your current offset from GMT)
In your specific case it looks like you are in a Mountain Time (GMT offset of 7). So if I paste your value given of 1362161251894 in a new Excel spreadsheet in cell A1 and then paste the following formula, I get a result of 41333.46356, which if I then tell Excel to format as a Date (press ctrl+1 on the cell) is: 2/28/13 11:07 AM
I read this too quickly and didn't realize this was for milliseconds. If your timestamp is in seconds, be sure to remove the /1000 after x .
@rymo There is no need for the formula to take into account how Excel's host system starts counting dates. Excel expresses DATEVALUE as whole numbered days and fractional time, so DATEVALUE("1-1-1904") on Mac would subtract 1 day off of the result, which is not what the OP wants AFAIK.
I noticed the same thing as @KDP -- my time of day is correct, but the day (date) is reported as one day sooner than it should be (i.e., reports 6/21 when it should be 6/22). What's causing this and how do I "fix" it? Thanks!
This works if you convert from milliseconds to seconds on your epoch time-stamp. To make it work correctly for milliseconds, you should do: =(A1/86400000)+25569 and remember that this doesn't take into account timezone.
Epoch time was provided in milliseconds ( 1523060201838 ). User is looking to Convert for NY Time Zone (GMT -04:00 or DST -14400).
Cell A1 on Excel has the 13 digit value epoch time.
converts to 4/6/18 8:16 PM (after you format cell as Date).
This way works every time in Excel 2016. +1. Thanks, mate! [Also, a note to others: replace 14400 with 3600*x , where x is your offset from GMT. In this example, EST is GMT -4, and 3600*4 is 14400 .]
I have found that the easiest formulas to remember and read are these:
Convert from Unix/Epoch timestamp to date in Excel (assuming timestamp is in A2):
This will generate the GMT Excel time value. You then need to set the format for the cell to one of the date formats, or create your own custom format. I tend to use either:
Convert from date to Epoch timestamp (assuming date is in A2):
I have found that this page is pretty helpful: Extendoffice excel-timestamp-to-date
Note: If you need to adjust to local time from GMT (New York as an example), add the difference at the end. The following show a -5 hour offset.
it will help for local timing like India is GMT + 5:30.
Excel expresses days as whole values and times as fractional values. Therefore, if the epoch time was provided in milliseconds since 1-1-1970 from UNIX, then we want to divide by the number of milliseconds in a year and add Excel's representation of 1-1-1970 to provide the human readable UTC time. If your value is in cell A1, then Excel would need:
Note you can drop the /1000 if your epoch time was in seconds rather than milliseconds. To convert to local time, where 't' is your local UTC offset (remember to use a negative value if you have a negative UTC offset), you can add/subtract the UTC offset:
Note that your UTC offset may vary based on whether daylight saving time is observed in your location, making this an incomplete solution for showing local times throughout the year in a single spreadsheet.
Does anyone know how to convert an Excel date to a correct Unix timestamp?
What do you mean by an "excel date"? Do you mean text formatted as a human-readable date-time string like "11/09/2009 3:23:24 PM" ?
Don't forget that on January 19, 2038 the Unix Time Stamp will cease to work due to a 32-bit overflow. Before this moment millions of applications will need to either adopt a new convention for time stamps or be migrated to 64-bit systems which will buy the time stamp a "bit" more time.
11 Answers 11
Non of these worked for me. when I converted the timestamp back it's 4 years off.
This worked perfectly: =(A2-DATE(1970,1,1))*86400
Don't forget your time zone (unless you're in UTC and not observing Summer Time). UNIX epoch is agnostic to the time zone whereas spreadsheets are not. Here is a guide with examples.
Modern versions of Excel will complain of the formula above unless you replace commas with semicolons: (1970;1;1)
@tonygil It'll work if the cells you're targeting are actually dates in Excel. If it's text that's supposed to represent a date all bets are off.
Windows and Mac Excel (2011):
Office 2011 on Mac seems to work fine with the "Windows" version of these formulas. The "Mac" versions are way off on it.
I think you've reversed the excel and unix timestamps in those formulas. The Unix timestamp a number of seconds, and so needs to be divided by 86400, rather than the excel timestamp, which is what the formulas say. See @radicand's answer.
Thanks for the comments, tested this on Mac Excel 2011 and it looks like they are the same as the Windows version.
If we assume the date in Excel is in A1 cell formatted as Date and the Unix timestamp should be in a A2 cell formatted as number the formula in A2 should be:
= (A1 * 86400) - 2209075200
86400 is the number of seconds in the day 2209075200 is the number of seconds between 1900-01-01 and 1970-01-01 which are the base dates for Excel and Unix timestamps.
The above is true for Windows. On Mac the base date in Excel is 1904-01-01 and the seconds number should be corrected to: 2082844800
Mac Excel default date base is 1904 whereas in Windows Excel it is 1900, but you can change the date base in Mac Excel by unchecking "use 1904 date system" under preferences/calculation, then it works like windows excel.
Here is a mapping for reference, assuming UTC for spreadsheet systems like Microsoft Excel:
* “Jan Zero, 1900” is 1899/12/31; see the Bug section below. † Excel 2011 for Mac (and older) use the 1904 date system.
As I often use awk to process CSV and space-delimited content, I developed a way to convert UNIX epoch to timezone/DST-appropriate Excel date format:
I used echo for this example, but you can pipe a file where the first column (for the first cell in .csv format, call it as awk -F, ) is a UNIX epoch. Alter $1 to represent your desired column/cell number or use a variable instead.
This makes a system call to date . If you will reliably have the GNU version, you can remove the 2>/dev/null || date … +%%z and the second , $1 . Given how common GNU is, I wouldn't recommend assuming BSD's version.
The getline reads the time zone offset outputted by date +%z into tz , which is then translated into hours . The format will be like -0700 (PDT) or +0530 (IST), so the first substring extracted is 07 or 05 , the second is 00 or 30 (then divided by 60 to be expressed in hours), and the third use of tz sees whether our offset is negative and alters hours if needed.
The formula given in all of the other answers on this page is used to set excel , with the addition of the daylight-savings-aware time zone adjustment as hours/24 .
If you're on an older version of Excel for Mac, you'll need to use 24107 in place of 25569 (see the mapping above).
To convert any arbitrary non-epoch time to Excel-friendly times with GNU date:
This is basically the same code, but the date -d no longer has an @ to represent unix epoch (given how capable the string parser is, I'm actually surprised the @ is mandatory; what other date format has 9-10 digits?) and it's now asked for two outputs: the epoch and the time zone offset. You could therefore use e.g. @1234567890 as an input.
Lotus 1-2-3 (the original spreadsheet software) intentionally treated 1900 as a leap year despite the fact that it was not (this reduced the codebase at a time when every byte counted). Microsoft Excel retained this bug for compatibility, skipping day 60 (the fictitious 1900/02/29), retaining Lotus 1-2-3's mapping of day 59 to 1900/02/28. LibreOffice instead assigned day 60 to 1900/02/28 and pushed all previous days back one.
Any date before 1900/03/01 could be as much as a day off:
Excel doesn't acknowledge negative dates and has a special definition of the Zeroth of January (1899/12/31) for day zero. Internally, Excel does indeed handle negative dates (they're just numbers after all), but it displays them as numbers since it doesn't know how to display them as dates (nor can it convert older dates into negative numbers). Feb 29 1900, a day that never happened, is recognized by Excel but not LibreOffice.
Читайте также: