Как преобразовать числовую строку в дату и время в Excel?
В повседневной работе с Excel часто возникают ситуации, когда даты или время вводятся как сплошные числовые строки (например, «20110312») — это ускоряет ввод данных. Хотя такой подход действительно повышает скорость и эффективность при быстрой фиксации информации, Excel не распознаёт эти строки как даты, что может привести к путанице или ошибкам при последующих вычислениях, форматировании или использовании функции Анализ данных. Поэтому для корректной обработки данных или создания единообразных книг такие числовые строки зачастую необходимо преобразовать в стандартные форматы даты или даты и времени, поддерживаемые Excel.
Ниже — практические пошаговые решения для эффективного преобразования числовых строк в стандартные форматы даты и/или времени в Excel, охватывающие как простые, так и сложные случаи — с использованием встроенных средств и дополнительных инструментов:
Преобразование числовой строки, например 20110312, в стандартный формат даты
Преобразование числовой строки, такой как 40614,43, в формат даты и времени
Преобразование числовой строки 20110312 в формат даты с помощью функции «Текст по столбцам»
При работе с наборами данных, импортированными из других систем или введёнными вручную, вы можете обнаружить, что даты хранятся как сплошные числовые строки (например, «20110312»), которые Excel не распознаёт автоматически как даты. Функция «Текст по столбцам» предлагает простой способ преобразовать такие числовые строки в формат даты, если последовательность цифр соответствует известному шаблону, например ГГГГММДД.
1. Выделите числовые строки, которые нужно преобразовать в даты, и нажмите Данные > Текст по столбцам на ленте. Этот инструмент идеально подходит для обработки данных по столбцам и позволяет одновременно преобразовывать несколько ячеек.
2. В диалоговом окне «Текст по столбцам» дважды нажмите Далее, чтобы перейти к шагу 3 из 3. На этом шаге установите флажок Дата, затем выберите ГМД из списка (это соответствует порядку год-месяц-день в строке «20110312»).
3. Нажмите Готово. Выделенные ячейки мгновенно преобразуются в стандартный формат даты.
Если результат преобразования не соответствует ожиданиям, проверьте структуру исходной строки и выбранный порядок даты. Также убедитесь, что к ячейкам не применено текстовое форматирование, которое может помешать преобразованию.
Преобразование числовой строки в стандартную дату с помощью Kutools для Excel
Если у вас большой диапазон числовых строк из разных источников или шаблоны таких строк сложны и несогласованы, встроенные средства могут оказаться слишком трудоёмкими или не справиться с корректным распознаванием всех типов дат. В таких случаях функция Kutools для Excel’s Распознавание даты предлагает удобное и эффективное решение, поддерживающее более широкий спектр нестандартных форматов дат и пакетную обработку.
После бесплатной установкиKutools для Excel выполните следующие действия:
Выделите строки с датами или числами, которые нужно преобразовать в стандартный формат даты, затем нажмите Kutools > Содержимое > Распознавание даты в меню.
Выделённые числовые строки мгновенно преобразуются в формат даты, распознаваемый Excel, — его можно дополнительно настроить или использовать для вычислений.
Это решение особенно полезно при работе со смешанными или сложными Формат даты, поскольку устраняет необходимость выполнять отдельные пошаговые операции и помогает избежать ошибок, вызванных несогласованным Исходные данные. Если некоторые элементы остаются в виде текста, убедитесь, что они не содержат скрытых символов или неожиданных разделителей.
Формула Excel — преобразование числовой строки, например 20110312, в дату
Если вы предпочитаете подход на основе формул или вам нужно автоматизировать преобразование для определённой структуры, такой как ГГГГММДД (например, «20110312»), используйте комбинацию функций ДАТА, ЛЕВСИМВ, ПСТР и ПРАВСИМВ. Этот метод идеально подходит для случаев, когда необходимо сохранить исходные данные без изменений и отображать преобразованную дату в отдельном столбце.
1. В новой пустой ячейке (например, B1), расположенной рядом с числовой строкой (в ячейке A1), введите следующую формулу:
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))2. Нажмите клавишу Enter для подтверждения. Если требуется преобразовать несколько строк, перетащите маркер заполнения вниз, чтобы применить формулу к другим ячейкам. Это преобразует каждую числовую строку в правильно отформатированную дату.
Если ваши данные представлены в формате, отличном от ГГГГММДД, скорректируйте формулу соответствующим образом (например, поменяйте местами функции)ЛЕВСИМВ, ПСТР и ПРАВСИМВ в зависимости от порядка компонентов даты). Убедитесь, что ваша числовая строка состоит ровно из 8 цифр и содержит только цифры — это поможет избежать ошибок в формуле.
Преобразование числовой строки 40614,43 в дату и время с помощью Установить формат ячейки
Иногда даты и время хранятся как серийные числа с десятичными значениями (например, «40614,43»). В Excel целая часть представляет дату, а десятичная — время. Чтобы корректно отобразить и дату, и время в одной ячейке, можно настроить Формат ячеек с помощью функции Установить формат ячейки.
1. Выделите число или диапазон, который нужно преобразовать и отобразить как дату и время, затем нажмите Ctrl + 1(или щёлкните правой кнопкой мыши и выберите)Установить формат ячейки), чтобы открыть диалоговое окно. На вкладке Число выберите Другой в списке категорий, затем введите мм/дд/гггг чч:мм в поле Тип.
При необходимости вы можете изменить формат отображения (например, добавить секунды — используйте «мм/дд/гггг чч:мм:сс»).
2. Нажмите ОК. Теперь ячейка(и) будет отображать читаемую информацию о дате и времени вместо неотформатированного числа.
Если результат отображается как «#####» или в виде неожиданных значений, убедитесь, что ширина ячейки достаточна и к ней не применено конфликтующее форматирование.
Формула Excel — извлечение даты и времени по отдельности из серийного номера даты с десятичной частью
Если у вас есть серийные номера дат Excel с десятичными дробями (например, «40614,43»), целая часть обозначает дату, а дробная — время. С помощью простых формул вы легко разделите их на два читаемых значения — идеальное решение при импорте необработанных данных, файлов журналов или автоматизированных отчётов.
Чтобы извлечь дату:
1. Введите следующую формулу в пустую ячейку (например, B1), если исходное значение находится в ячейке A1:
=INT(A1)2. Отформатируйте эту ячейку как дату: щёлкните правой кнопкой мыши, выберите Установить формат ячейки, укажите Дата и выберите желаемый формат даты.
Чтобы извлечь время:
1. Введите эту формулу в следующую ячейку (например, C1):
=A1-INT(A1)2. Отформатируйте эту ячейку как время: щёлкните правой кнопкой мыши, выберите Установить формат ячейки, укажите Время и задайте формат, например «чч:мм:сс».
Так вы сможете быстро отображать компоненты даты и времени по отдельности. Убедитесь, что исходное число представляет собой корректный серийный номер даты Excel с десятичной частью, чтобы воспользоваться этим методом.
Лучшие инструменты повышения продуктивности в 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек