#excel #date #datetime #excel-formula
#excel #Дата #дата-время #excel-формула
Вопрос:
ВСЕ. Я разработчик интерфейса для здравоохранения, использующий данные в формате HL7. Много раз я экспортировал данные, содержащие даты или даты в текстовом формате. Это доступно в трех вариантах:
- 16-символьные D / T: например, 20201225120000 для 25 декабря 2020 года в 12:00:00
- 14-символьный D / T: например, 202012251200 для 25 декабря 2020 года в 12:00
- 8-символьный D / T: например, 20201225 для 25 декабря.
Несколько раз я выполнял поиск в Интернете, чтобы найти, как преобразовать эти значения в Excel. Это неудобно, потому что 16-значные временные метки обычно находятся в формате Unix. Итак, я нахожу много ответов о преобразовании этих значений… но вам трудно найти правильный ответ на этот вопрос.
Со временем я создал текстовый файл, содержащий ответы. Итак, в свободную минуту во время этой пандемии я решил опубликовать ответы здесь. Надеюсь, это поможет другим разработчикам, и, надеюсь, это будет отображаться в результатах поиска, когда у меня нет текстового файла под рукой.
Комментарии:
1. Кстати:
2020122512000014 цифр и20201225120012.2. Спасибо! Вы правы… Я также получаю их в миллисекундах. Мне нравится ваше решение! Это будет в моем текстовом файле. 🙂
3. Если вы не возражаете потерять миллисекунды, моя формула будет работать и для них.
Ответ №1:
Это выполнит все три:
=--REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LEFT(A1amp;"000000",14),13,0,":"),11,0,":"),9,0," "),7,0,"-"),5,0,"-")
Ответ №2:
Есть три способа справиться с этим.
1) Используйте пользовательский формат Excel, чтобы значения отображались в удобочитаемом виде без фактического изменения значения.Щелкните правой кнопкой мыши ячейку или столбец, затем выберите «Формат ячеек», затем в разделе «Категория» выберите «Пользовательский». Вставьте соответствующий формат, указанный ниже.
2) Преобразование в дату и время с помощью функций обработки текста Excel.Вставьте новый столбец справа от текстового столбца даты и времени. Вставьте формулу Excel в новый столбец.
3) Преобразуйте в значение даты и времени Excel путем умножения, затем измените столбец на формат даты или пользовательский формат даты и времени. Вставьте новый столбец справа от текстового столбца даты и времени. Вставьте формулу Excel в новый столбец.
Вот способы для каждого из трех вариантов длины.
16 «Цифр» Пример: 20201225120000 или 2020-12-25 12:00:00
Пользовательский формат:
####-##-## ##:##:##
Формула для преобразования в дату и время Excel (измените K2 на правильный идентификатор ячейки)
= ДАТА (СЛЕВА (K2,4), СЕРЕДИНА (K2,5,2), СЕРЕДИНА (K2,7,2)) ВРЕМЯ (СЕРЕДИНА (K2,9,2), СЕРЕДИНА (K2,11,2), СПРАВА (K2,2))
Умножение для преобразования в дату и время (измените K2 на правильный идентификатор ячейки)
= Сумма (K2 * 0.000000000030752269457537)
14 цифр
Пример: 202012251200 или 2020-12-25 12:00
Пользовательский формат
####-##-## ##:##
Формула для преобразования в дату и время Excel (измените K2 на правильный идентификатор ячейки)
= ДАТА (СЛЕВА (K2,4), СЕРЕДИНА (K2,5,2), СЕРЕДИНА (K2,7,2)) ВРЕМЯ (СЕРЕДИНА (K2,9,2), СПРАВА (K2,2))
Умножение для преобразования в дату и время (измените K2 на правильный идентификатор ячейки)
= Сумма (K2 * 0.000000218676875311879)
8-значную дату
Пример: 202012251200 или 2020-12-25
Пользовательский формат
####-##-##
Формула для преобразования даты и времени в Excel
= ДАТА (СЛЕВА (K2,4), СЕРЕДИНА (K2,5,2), СПРАВА (K2,2))
Умножение для преобразования в дату и время
= Сумма (K2 * 0,002186750199616760000)
Я надеюсь, что это кому-то поможет. Если у вас есть другой вариант, пожалуйста, оставьте его ниже в качестве альтернативы!