Как timestamp перевести в дату в excel

Обновлено: 07.07.2024

Does anyone know how to convert an Excel date to a correct Unix timestamp?

871 1 1 gold badge 7 7 silver badges 3 3 bronze badges

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

2,386 10 10 gold badges 45 45 silver badges 75 75 bronze badges current time for CDT (central daylight time): =(NOW()-DATE(1970,1,1))*86400 + 5*3600 Modern versions of Excel will complain of the formula above unless you replace commas with semicolons: (1970;1;1)

Windows and Mac Excel (2011):

6,343 3 3 gold badges 30 30 silver badges 53 53 bronze badges Office 2011 on Mac seems to work fine with the "Windows" version of these formulas. The "Mac" versions are way off on it. Thanks for the comments, tested this on Mac Excel 2011 and it looks like they are the same as the Windows version. Is there a way to transform a unix timstamp in millisecond to datetime, preserving the milliseconds? says the man on stackoverflow. yeah, what about leap seconds, professor.

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

5,857 3 3 gold badges 15 15 silver badges 8 8 bronze badges 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.

Временная метка Unix также называется временем эпохи или временем POSIX, которое широко используется во многих операционных системах или форматах файлов. В этом руководстве рассказывается о преобразовании между датой и меткой времени Unix в Excel.

Преобразовать дату в метку времени

Чтобы преобразовать дату в метку времени, формула может вычислить это.

Выберите пустую ячейку, предположим, ячейку C2, и введите эту формулу = (C2-ДАТА (1970,1,1)) * 86400 в это и нажмите Enter key, при необходимости вы можете применить диапазон с этой формулой, перетащив маркер автозаполнения. Теперь диапазон ячеек даты преобразован в метки времени Unix.

Удалить время из DateTime

В Excel, чтобы удалить 12:11:31 из 1 21:2017:12 и сделать это точно 11, вам может потребоваться некоторое время, чтобы создать формулу для выполнения этой задачи. Тем не менее Удалить время из даты полезности Kutools for Excel может быстро удалить временную метку из форматирования даты и времени в Excel.
Нажмите, чтобы загрузить 30-дневную бесплатную пробную версию.

Преобразование даты и времени в метку времени

Существует формула, которая может помочь вам преобразовать дату и время в метку времени Unix.

1. Во-первых, вам нужно ввести всемирное координированное время в ячейку, 1. Смотрите скриншот:

2. Затем введите эту формулу = (A1- $ C $ 1) * 86400 в ячейку, нажмите Enter , а затем при необходимости перетащите дескриптор автозаполнения в диапазон с этой формулой. Смотрите скриншот:

Советы: В формуле A1 - это ячейка даты и времени, C1 - введенная вами координата всемирного времени.

Преобразовать метку времени на дату

Если у вас есть список временных меток, необходимых для преобразования на сегодняшний день, вы можете сделать следующие шаги:

1. В пустой ячейке рядом со списком временных меток введите эту формулу. =(((A1/60)/60)/24)+DATE(1970,1,1) , Нажмите Enter и перетащите маркер автозаполнения в нужный диапазон.

2. Затем щелкните правой кнопкой мыши ячейки, в которых используется формула, и выберите Формат ячеек из контекстного меню, затем во всплывающем Формат ячеек диалог под Nвокруг вкладку нажмите Время в Категория список, затем выберите тип даты в правом разделе.

3. Нажмите OK, теперь вы можете видеть, что метки времени Unix преобразованы в даты.

Ноты:

1. A1 указывает нужную ячейку с отметкой времени.

2. Эту формулу также можно использовать для преобразования ряда временных меток в дату и время, просто отформатируйте результат в формате даты и времени.

Наконечник: Если у вас проблемы с запоминанием сложных формул, здесь Авто текст инструмент Kutools for Excel Вы можете сохранить все формулы, которые вы использовали, на панели для вас, а затем вы можете повторно использовать их в любом месте в любое время, вам нужно только изменить ссылки в соответствии с вашими реальными потребностями. Нажмите, чтобы бесплатно скачать сейчас.

Относительные статьи:

Как преобразовать дату и время из одного часового пояса в другой в Excel?
Эта статья покажет вам, как преобразовать дату и время из одного часового пояса в другой в Excel.

Как разделить дату и время из ячейки на две отдельные ячейки в Excel?
Например, у вас есть список данных, смешанных с датой и временем, и вы хотите разделить каждую из них на две ячейки, одна - это дата, а другая - время, как показано ниже. В этой статье представлены два быстрых метода решения этой проблемы в Excel.

Как преобразовать ячейку формата даты / времени в дату только в Excel?
Если вы хотите преобразовать ячейку формата даты и времени только в значение даты, такое как 2016/4/7 1:01 AM до 2016/4/7, эта статья может вам помочь.

Как убрать время с даты в Excel?
Если есть столбец даты с отметкой времени, например, 2 17:2012, и вы не хотите сохранять отметку времени и хотите удалить время 12:23 из даты и оставить только дату 12. Как можно быстро удалить время из даты в нескольких ячейках Excel?

Как объединить дату и время в одну ячейку в Excel?
На листе есть два столбца, один - это дата, другой - время. Есть ли способ быстро объединить эти два столбца в один и сохранить формат времени? Теперь в этой статье представлены два способа в Excel объединить столбец даты и столбца времени в один и сохранить формат времени.

Если вы хотите преобразовать ячейку формата даты и времени только в значение даты, такое как 2016/4/7 1:01 AM до 2016/4/7, эта статья может вам помочь.

Преобразование ячейки формата даты / времени в дату только с помощью формулы

Следующая формула поможет вам преобразовать ячейку формата даты / времени в дату только в Excel.

1. Выберите пустую ячейку, в которую вы поместите значение даты, затем введите формулу. = МЕСЯЦ (A2) & "/" & ДЕНЬ (A2) & "/" & ГОД (A2) в строку формул и нажмите Enter ключ.

2. Вы можете видеть, что только значение даты заполняется в выбранной ячейке на основе ячейки даты / времени. Продолжайте выбирать ячейку даты, затем перетащите маркер заполнения вниз, чтобы отобразить все даты, основанные на других ячейках даты / времени. Смотрите скриншот:

Легко удаляйте все времена из дат всего несколькими щелчками мыши

В этом разделе показано, как легко удалить все времена из выбранных ячеек даты с помощью утилиты Удалить время из даты в Kutools for Excel. Пожалуйста, сделайте следующее.

Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.

1. Выберите пустую ячейку для определения даты после удаления времени из (говорит C2). Нажмите Kutools > Формула Помощник > Формула Помощник. Смотрите скриншот:

2. в Помощник по формулам диалоговое окно, настройте следующим образом.

В Выберите формулу коробка, найдите и выберите Удалить время из даты;
Советы: Вы можете проверить Фильтр введите определенное слово в текстовое поле, чтобы быстро отфильтровать формулу.
Выберите ячейку с датой и временем, вам нужно сохранить только дату в DateTime коробка;
Нажмите OK кнопку.

3. Продолжайте выбирать первую ячейку результата, затем перетащите маркер заполнения вниз, чтобы получить все результаты, как вам нужно, как показано ниже:

Теперь все времена удалены из указанных дат.

Если вы хотите получить 30-дневную бесплатную пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.

Преобразование ячейки формата даты / времени в дату только с помощью Kutools for Excel

Вот рекомендую удобный инструмент - Kutools for Excel, С этими Применить форматирование даты Утилита, вы можете легко преобразовать ячейку формата даты / времени в любой формат даты, который вам нужен.

Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.

1. Выберите ячейку формата даты / времени, в которой будет отображаться только значение даты, затем щелкните Kutools > Формат > Применить форматирование даты. Смотрите скриншот:

2. в Применить форматирование даты диалоговом окне выберите формат даты в Форматирование даты поле, а затем щелкните OK кнопку.

Теперь ячейки формата даты / времени отображаются только как значение даты. Смотрите скриншот:

Внимание: Хотя ячейки формата даты / времени отображаются только как дата, отображаемые значения в строке формул по-прежнему имеют формат даты / времени. Если вы хотите преобразовать значения, отображаемые в строке формул, в их фактические значения, выберите их и щелкните Kutools > К фактическому. Смотрите скриншот:

Trying to convert 1504865618099.00 Unix time into a readable date time. I tried this:

But it's not working.

898 1 1 gold badge 6 6 silver badges 19 19 bronze badges

5 Answers 5

To convert the epoch(Unix-Time) to regular time like for the below timestamp

First convert the value with the following function like below

The output will be like below

Now Add the formula like below

Now format the cell like below or required format(Custom format)

Now example time comes like

The three zeros are for milliseconds

2,192 1 1 gold badge 20 20 silver badges 27 27 bronze badges

=A1/(24*60*60) + DATE(1970;1;1) should work with seconds.

=(A1/86400/1000)+25569 if your time is in milliseconds, so dividing by 1000 gives use the correct date

This worked for me, but only when I replaced the semicolons in the DATE function with commas.

. and the format of the cell should be date.

If it doesn't work for you

Explanation

Unix system represent a point in time as a number. Specifically the number of seconds* since a zero-time called the Unix epoch which is 1/1/1970 00:00 UTC/GMT . This number of seconds is called "Unix timestamp" or "Unix time" or "POSIX time" or just "timestamp" and sometimes (confusingly) "Unix epoch".

In the case of Excel they chose a different zero-time and step (because who wouldn't like variety in technical details?). So Excel counts days since 24 hours before 1/1/0000 UTC/GMT . So 25569 corresponds to 1/1/1970 00:00 UTC/GMT and 25570 to 2/1/1970 00:00 .

Now please note that we have 86400 seconds per day (24 hours x60 minutes each x60 seconds) and you can understand what this formula does: A1/86400 converts seconds to days and +25569 adjusts for the offset between what is time-zero for Unix and what is time-zero for Excel.

By the way DATE(1970,1,1) will helpfully return 25569 for you in case you forget all this so a more "self-documenting" way to write our formula is:

Читайте также: