Как преобразовать дату в формате ГГГГММДДЧЧММСС в обычный формат даты и времени в Excel?
На практике часто встречаются данные, экспортированные из других систем или файлов журналов, где дата и время представлены единым непрерывным строковым значением в формате yyyymmddhhmmss. Например, строка «20240630174521» соответствует 30,06.2024 17:45:21. Однако Excel не распознаёт такой формат автоматически как стандартное значение даты и времени, что затрудняет анализ, сортировку и вычисления. Чтобы эффективно анализировать данные, фильтровать их или строить отчёты, такие строки необходимо преобразовать в понятный Excel формат даты и времени — например, dd/mm/yyyy hh:mm:ss.
В этой статье вы найдёте пошаговые инструкции по преобразованию строк в формате yyyymmddhhmmss в стандартный формат даты и времени Excel. Мы рассмотрим несколько подходов: от простых формул для мгновенного преобразования прямо в ячейках до автоматизированных решений с помощью кода VBA, а также использование Power Query — гибкого и наглядного инструмента для обработки данных. Каждый метод идеально подходит для определённых сценариев в зависимости от ваших задач, объёма данных и уровня владения возможностями Excel.
Преобразование yyyymmddhhmmss в обычный формат Установить формат даты и времени с помощью формул
Код VBA — автоматическое преобразование yyyymmddhhmmss в обычный формат Дата и время
Преобразование yyyymmddhhmmss в обычный формат Установить формат даты и времени с помощью формул
Формулы Excel позволяют быстро и надёжно преобразовать строку вида yyyymmddhhmmss в корректное значение даты и времени, распознаваемое Excel, прямо на листе — без внешних надстроек или Расширенных инструментов. Такой подход идеален для небольших наборов данных или когда важно обеспечить ручное, прозрачное и легко проверяемое преобразование.
Чтобы преобразовать строку, введите следующую формулу в пустую ячейку (например, в ячейку B2, если исходное значение yyyymmddhhmmss находится в ячейке A2):
=TEXT(DATE(LEFT(A2,4),MID(A2,5,2),MID(A2,7,2))+TIME(MID(A2,9,2),MID(A2,11,2),MID(A2,13,2)),"dd/mm/yyyy hh:mm:ss")Инструкции: После ввода формулы нажмите клавишу Enter. Если у вас в столбце A есть список значений в формате yyyymmddhhmmss, просто перетащите маркер заполнения из ячейки B2 вниз, чтобы мгновенно применить формулу ко всем остальным строкам. Каждая формула преобразует исходное значение в отформатированную дату и время в виде текста.
Этот метод идеально подходит для разовых задач по очистке данных, небольших списков или случаев, когда вы хотите оставить исходную строку в одном столбце, а отформатированное значение — в другом. Возможный недостаток: результат формулы остаётся текстом; если вам понадобится истинное значение даты и времени Excel для расчётов (например, для определения разницы во времени), текст придётся преобразовать в числовой формат.
Пояснение параметров и советы:
- LEFT(A2,4): извлекает 4-значный год
- MID(A2,5,2): извлекает 2-значный месяц
- MID(A2,7,2): извлекает 2-значный день
- MID(A2,9,2): извлекает 2-значный час
- MID(A2,11,2): извлекает 2-значную минуту
- MID(A2,13,2): извлекает 2-значную секунду
- Функция TEXT() форматирует результат в виде удобочитаемой строки даты и времени.
Возможные проблемы: Если какие-либо исходные значения содержат не ровно 14 цифр (например, отсутствуют ведущие нули или значения случайно введены в экспоненциальной форме), формулы могут выдать ошибки или неверные результаты. Перед применением формульного преобразования обязательно убедитесь в согласованности входных данных.
Альтернативно, если ваши данные всегда содержат ровно 14 цифр, используйте this shorter formula converts the string and returns an Excel serial datetime value (not text):
=--TEXT(A2,"0000\-00\-00\ 00\:00\:00")Как использовать: После ввода этой формулы в ячейку (например, B2) нажмите клавишу Enter. Затем, чтобы отобразить результат в удобочитаемом формате даты и времени, возможно, потребуется отформатировать получившиеся ячейки как ДД/ММ/ГГГГ ЧЧ:ММ:СС с помощью диалогового окна «Установить формат ячейки». Для этого выделите ячейки с результатами, щелкните правой кнопкой мыши и выберите Установить формат ячейки → Другие числовые форматы, после чего введите dd/mm/yyyy hh:mm:ss в качестве кода формата.
Этот второй метод особенно эффективен, если вам нужно получить именно значение даты, а не текстовую строку.
Совет: Если вы планируете использовать преобразованные дату и время в дальнейших вычислениях (например, для вычитания дат или определения продолжительности), всегда применяйте формулу, возвращающую порядковый номер (вторая формула), а затем — при необходимости — задавайте нужное форматирование.
Устранение неполадок: Если возникают ошибки, убедитесь, что ваши исходные данные действительно являются текстом и не содержат нечисловых символов. Начальные или конечные пробелы также могут повлиять на результаты; если вы подозреваете эту проблему, очистите данные с помощью функции TRIM(A2) внутри формулы.
Код VBA — автоматическое преобразование yyyymmddhhmmss в обычный формат Дата и время
Для пользователей, которым необходимо обрабатывать большие объёмы данных или автоматизировать преобразование, VBA (Visual Basic for Applications) предлагает гибкое решение. Пройдя циклом по выделенным ячейкам, можно мгновенно преобразовать каждое значение в формате yyyymmddhhmmss в стандартную дату и время Excel — прямо на месте или в указанном целевом столбце. Этот подход особенно удобен, если вы хотите избежать размещения формул в множестве ячеек или если ваши данные регулярно обновляются.
Этот метод идеально подходит для работы с большими наборами данных или выполнения регулярно повторяющихся задач. Однако для использования VBA необходимо включить макросы, что может быть ограничено политиками вашей организации или настройками Excel. Обязательно сохраните файл перед запуском любого кода VBA — отменить внесённые изменения не всегда просто.
Этапы выполнения:
- Выделите на листе диапазон значений в формате yyyymmddhhmmss, которые требуется преобразовать (например, A2:A100).
- Нажмите Разработчик → Visual Basic, чтобы открыть редактор VBA. В левой панели найдите свою книгу, щелкните по ней правой кнопкой мыши и выберите Вставить → Модуль. В окне кода вставьте следующий код:
Sub Convert_yyyymmddhhmmss_To_DateTime()
Dim rng As Range
Dim cell As Range
Dim strDate As String
Dim y As Integer
Dim m As Integer
Dim d As Integer
Dim hh As Integer
Dim mm As Integer
Dim ss As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.InputBox("Select the range of yyyymmddhhmmss values to convert:", xTitleId, Selection.Address, Type:=8)
For Each cell In rng
strDate = Trim(cell.Value)
If Len(strDate) = 14 And IsNumeric(strDate) Then
y = CInt(Left(strDate, 4))
m = CInt(Mid(strDate, 5, 2))
d = CInt(Mid(strDate, 7, 2))
hh = CInt(Mid(strDate, 9, 2))
mm = CInt(Mid(strDate, 11, 2))
ss = CInt(Mid(strDate, 13, 2))
cell.Value = DateSerial(y, m, d) + TimeSerial(hh, mm, ss)
cell.NumberFormat = "dd/mm/yyyy hh:mm:ss"
End If
Next cell
End SubПосле вставки кода закройте редактор VBA. Вернитесь в Excel и запустите макрос: нажмите Alt + F8, выберите «Convert_yyyymmddhhmmss_To_DateTime» и щёлкните Выполнить. Макрос предложит выбрать диапазон и преобразует каждую 14-значную числовую строку прямо на месте в удобочитаемое значение даты и времени.
Советы и устранение неполадок:
- Если некоторые значения не преобразуются, убедитесь, что их длина составляет ровно 14 цифр, проверьте отсутствие лишних пробелов и подтвердите, что все значения являются числами.
- При необходимости вы можете скопировать преобразованные значения даты и времени в другой столбец до запуска макроса, чтобы исходные данные остались без изменений.
- Всегда сохраняйте книгу перед запуском макросов и убедитесь, что макросы разрешены в Excel.
Преимущества: Быстрая обработка больших диапазонов и простая автоматизация повторяющихся задач.
Недостатки: Требуется включение макросов; решение не всегда применимо в средах с ограничениями безопасности макросов.
Раскройте магию Excel с помощью KUTOOLS AI
- Интеллектуальное выполнение: Выполняйте операции с ячейками, анализируйте данные и создавайте диаграммы — всё это доступно через простые команды.
- Пользовательские формулы: создавайте индивидуальные формулы для оптимизации рабочих процессов.
- Программирование на VBA: Пишите и внедряйте код VBA легко и без усилий.
- Анализ формул: Легко разбирайтесь даже в самых сложных формулах.
- Перевод текста: Ломайте языковые барьеры прямо в ваших таблицах.
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек