Как преобразовать числовую строку в дату и время в Excel?
В повседневной работе с Excel часто возникают ситуации, когда даты или время вводятся как непрерывные числовые строки, например "20110312", чтобы ускорить ввод данных. Хотя этот прямой метод может повысить скорость и эффективность быстрой записи, эти числовые строки не распознаются Excel как даты и могут вызывать путаницу или ошибки при последующих вычислениях, форматировании или анализе данных. Поэтому для более формального использования данных или последовательных книг вам может потребоваться преобразовать такие числовые строки в стандартные, распознаваемые форматы даты или даты и времени в Excel.
Ниже вы найдете практические пошаговые решения для эффективного преобразования числовых строк в стандартные форматы даты и/или времени в Excel, охватывающие как простые, так и сложные случаи с использованием вспомогательных инструментов и без них:
Преобразование числовой строки, такой как 20110312, в стандартное форматирование даты
Преобразование числовой строки, подобной 40614.43, в формат даты и времени
Преобразование числовой строки 20110312 в формат даты с помощью Текст по столбцам
При работе с наборами данных, импортированными из других систем или введенными вручную, вы можете обнаружить, что даты хранятся как непрерывные числовые строки (например, "20110312"), которые автоматически не распознаются как даты в Excel. Функция Текст по столбцам является простым способом преобразования этих числовых строк в формат даты, если последовательность цифр соответствует известному шаблону, такому как ГГГГММДД.
1. Выберите числовые строки, которые вы хотите преобразовать в даты, и нажмите Данные > Текст по столбцам на ленте. Этот инструмент подходит для обработки данных в столбцах и может обрабатывать несколько ячеек одновременно.
2. В диалоговом окне Текст по столбцам просто нажмите Далее дважды, чтобы перейти к Шагу 3 из 3. На этом шаге установите флажок Дата, затем выберите ГМД из списка (это соответствует порядку год-месяц-день для "20110312").
3. Нажмите Завершить. Выбранные ячейки мгновенно преобразуются в стандартный формат даты. 
Если результат преобразования не соответствует ожиданиям, проверьте структуру исходной строки и выбранную последовательность дат. Также убедитесь, что ячейки не имеют текстового форматирования, которое может препятствовать преобразованию.
Преобразование числовой строки в стандартную дату с помощью Kutools для Excel
Когда у вас есть большой диапазон числовых строк из разных источников, или шаблоны числовых строк сложны и не согласованы, использование встроенных инструментов может быть трудоемким или не всегда правильно определять все типы дат. В таких случаях функция Преобразование в дату Kutools для Excel предоставляет удобное и эффективное решение, поддерживающее более широкий спектр нестандартных шаблонов дат и операций пакетной обработки.
После бесплатной установки 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-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек