Как преобразовать дату в числовой или текстовый формат в Excel?
Преобразование дат из Excel в текстовый или числовой формат — распространённая задача при обработке данных и подготовке отчётов. Например, вы могли импортировать данные, где даты представлены числами, или вам может понадобиться стандартизировать формат дат перед экспортом во внешние системы, созданием CSV-файлов или выполнением пакетных расчётов. В этом руководстве описаны несколько практических методов, охватывающих разные сценарии и требования преобразования, чтобы вы могли гибко выбрать наиболее подходящий вариант для своих задач.
Рассматриваются эффективность, простота и возможности пакетной обработки каждого метода, а также даются ключевые советы, меры предосторожности и рекомендации по устранению неполадок. Среди методов — формулы, инструменты форматирования, встроенные функции Excel и автоматизация с помощью VBA, которые значительно упростят преобразование дат в Excel.
- 1. Преобразование даты в текст
1,1 Преобразование даты в текст в формате мм/дд/гггг
1,2 Преобразование даты в текст в формате ммддгггг
1,3 Преобразование даты в текст в других форматах - 2. Преобразование даты в число
2,1 Преобразование даты в число в 5-значном формате
2,2 Преобразование даты в число в формате ммддгггг - 3. Преобразование даты в месяц/день/год или другой Формат даты за несколько кликов4
- 4. Текст по столбцам: преобразование значений даты
- 5. Скачайте образец файла
В этом разделе описаны ключевые способы преобразования дат в текстовые строки в Excel.
Самый простой способ преобразовать дату в текст — использовать функцию ТЕКСТ, которая позволяет гибко задавать выходной формат в зависимости от ваших потребностей и региональных настроек. Этот метод подходит для преобразования любого формата даты в читаемую текстовую строку и широко применяется при экспорте данных, подготовке сводок или быстрой стандартизации отображения дат.
Формула: =TEXT(date,«date_format»)
Ссылка:date: ячейка с датой, которую необходимо преобразовать в текст
date_format:требуемый формат отображения после преобразования даты в текст
Ниже — конкретные примеры, демонстрирующие эффективное использование этой формулы для различных задач форматирования. Обратите внимание: после применения формулы ТЕКСТ результат всегда возвращается в виде текстовой строки (а не числа), поэтому она идеально подходит для случаев, когда дальнейшие вычисления с датами не требуются.
1,1 Преобразование даты в текст в формате мм/дд/гггг
Чтобы преобразовать дату из ячейки A3 в строку формата мм/дд/гггг, введите следующую формулу в целевую ячейку (например, B3):
=TEXT(A3,«mm/dd/yyyy»)
Нажмите Enter, чтобы подтвердить формулу.
Если вы предпочитаете формат дд/мм/гггг, скорректируйте формулу следующим образом:
=TEXT(A8,«dd/mm/yyyy»)
После ввода формулы нажмите Enter.
Вы можете перетащить маркер заполнения, чтобы быстро применить формулу к нескольким ячейкам при одновременном преобразовании нескольких дат. Имейте в виду: результат будет представлен в виде текстовой строки, которую Excel по умолчанию выравнивает по левому краю. Если вы планируете использовать этот результат в дальнейших вычислениях, воспользуйтесь описанными ниже методами его преобразования в число.
1,2 Преобразование даты в текст в формате ммддгггг или ддммгггг
Если вы хотите преобразовать Дата вывода в компактный формат (например, ммддгггг), применяется тот же подход:
Введите эту формулу в целевую ячейку (например, B13):
=TEXT(A13,«mmddyyyy»)
Нажмите Enter, чтобы завершить.
Для формата ддммгггг введите:
=TEXT(A18,«ddmmyyyy»)
Затем нажмите Enter.
Если нужно применить операцию ко всему столбцу, просто скопируйте формулу вниз или воспользуйтесь маркером заполнения. В случае ошибок выравнивания или вычислений убедитесь, что все исходные ячейки с датами действительно содержат даты, а не текст, лишь отформатированный под дату.
1,3 Преобразование даты в текст в других форматах
Функция ТЕКСТ поддерживает множество форматов. Просто измените код формата в формуле — и настройте вывод под свои задачи! Это особенно полезно для создания строк даты, соответствующих различным региональным стандартам, подготовки документов или использования в других программах, где требуется фиксированный формат даты. Примеры:
| Date(A3) | 12/23/2019 |
| Формула | =TEXT(A3,«mm-dd-yyyy») | =TEXT(A3,«mm/dd») | =TEXT(A3,«dd») | =TEXT(A3,«d/m/y») |
| Результат | 23-12-2019 | 23/12 | 23 | 23/12/19 |
Если формула не возвращает нужный формат, внимательно проверьте строку формата: Excel требует двойные кавычки и корректные коды даты. Полный список параметров форматирования доступен в справке Excel (раздел «Пользовательский формат»).
В этом разделе рассматривается преобразование значений даты в числовые строки. В некоторых случаях может понадобиться получить серийный номер даты (для вычисления интервалов или обеспечения совместимости) или переформатировать даты как чистые числовые строки (например, для экспорта в базы данных, сортировки или передачи структурированных числовых данных в другое приложение).
2,1 Преобразование даты в число в 5-значном формате
Чтобы отобразить базовый серийный номер (количество дней с 1 января 1900 года), соответствующий дате, выполните следующие действия:
1. Щелкните правой кнопкой мыши ячейку с датой, которую нужно преобразовать, и выберите в контекстном меню Установить формат ячейки.
2. В диалоговом окне Установить формат ячейки перейдите на вкладку Число и выберите Общий в списке Числовые форматы.
3. Нажмите кнопку ОК. Дата в выделенной ячейке преобразуется в пятизначное число — это внутренний формат хранения дат в Excel. Вы можете использовать его для расчётов, например для определения разницы в днях путём вычитания дат, или для интеграции с системами, работающими с порядковыми номерами дат.
Примечание. Чтобы вернуть число к стандартному формату даты, просто повторите те же действия и выберите категорию «Дата».
2,2 Преобразование даты в число в формате mmddyyyy или ddmmyyyy
Если вы хотите преобразовать дату в строку цифр в формате mmddyyyy или ddmmyyyy (технически это всё ещё текстовая строка в Excel, но состоящая исключительно из цифр), используйте пользовательский числовой формат. Это особенно полезно для создания единообразных числовых кодов дат при сортировке, импорте/экспорте данных или формировании стандартизированных идентификаторов документов.
1. Щёлкните правой кнопкой мыши ячейку с датой, которую нужно преобразовать, и выберите в контекстном меню пункт Установить формат ячейки.
2. В диалоговом окне Установить формат ячейки на вкладке Число выберите Пользовательский в списке Числовые форматы. Затем введите нужный код формата, например mmddyyyy, в поле Тип.
3. Нажмите кнопку ОК. Теперь дата отображается в виде строки цифр в выбранном формате.
Вот несколько других пользовательских кодов Общие и соответствующих результатов:
| Дата | 2/23/2019 |
| Форматировать ячейку как пользовательский | ddmmyyyy | ddmmyy | mmyyyy | yyyymmdd |
| Отображение | 23022019 | 230219 | 022019 | 20190223 |
Будьте внимательны: хотя число отображается визуально, Excel по-прежнему воспринимает его как дату в ячейке, если вы не используете формулу ТЕКСТ для преобразования. При экспорте или использовании данных в другой системе убедитесь, считывается ли значение ячейки или её отображаемый текст.
Если вы хотите быстро преобразовать значения дат в месяц, день, год или любой другой пользовательский формат даты — даже при пакетной обработке — воспользуйтесь удобным инструментом Применить формат даты в Kutools для Excel. Это идеальное решение для тех, кто хочет избежать сложных формул и множества кликов правой кнопкой мыши, особенно при форматировании больших диапазонов дат в различных стилях.
После бесплатной установки Kutools для Excelвыполните следующие шаги:
1. Выделите ячейки с датами, которые нужно преобразовать.
2. Нажмите Kutools > Формат > Применить формат даты.
3. В диалоговом окне Применить формат даты выберите желаемый формат вывода на панели Формат даты. Результат можно сразу просмотреть на правой панели Предварительный просмотр.
4. Нажмите кнопку ОК, чтобы применить изменения — все выделенные даты будут немедленно переформатированы.
Советы. Этот метод особенно удобен для пакетной обработки и обеспечивает визуальный предварительный просмотр, исключая необходимость проб и ошибок. Если результаты окажутся неожиданными, проверьте исходные данные на наличие некорректных записей дат или дат, отформатированных как текст, так как такие значения могут быть неправильно распознаны инструментом.
Дополнительные советы по преобразованию дат
Встроенная функция Excel Текст по столбцам также позволяет пакетно преобразовывать даты в текст или другие форматы дат и чисел — например, при очистке импортированных данных с несогласованными форматами или подготовке к миграции в другие системы.
Преимущество этого метода в том, что он не требует формул или VBA и позволяет обрабатывать сразу несколько ячеек или целых столбцов. Однако он может перезаписать данные в столбцах справа, поэтому перед использованием убедитесь, что там нет важной информации, либо выполняйте преобразование в новой области.
Шаги:
- Выберите диапазон с датами, которые нужно преобразовать.
- Перейдите на вкладку Данные на ленте и нажмите Текст по столбцам.
- В мастере выберите С разделителями и нажмите Далее (не выбирайте никакие разделители на шаге 2 мастера).
- Нажмите Далее еще раз, затем в разделе Формат данных столбца выберите Дата и укажите правильный входной формат даты (например, МДГ, ДМГ или ГМД) либо выберите Текст, чтобы принудительно применить текстовый формат.
- Выберите ячейку назначения для вывода, если не хотите перезаписывать исходный столбец.
- Нажмите Готово.
После завершения выбранные значения дат будут преобразованы в указанный текстовый формат. Этот метод особенно эффективен для быстрого применения текстового формата к строкам с датами или для решения проблем с импортированными CSV- и TXT-файлами, где Excel может автоматически, но некорректно преобразовать текстовые даты.
Меры предосторожности. Если целевые столбцы содержат формулы или важные данные, заранее переместите их или создайте резервную копию. Для дат на неанглийском языке или пользовательских форматов дат дважды проверьте выпадающий список «Дата», чтобы он точно соответствовал расположению исходных данных. Если результаты окажутся неожиданными — например, даты отображаются как числа или не преобразуются, — убедитесь, что региональные параметры вашей системы (формат даты) совпадают с вариантом, выбранным в мастере.
В заключение, Excel предлагает множество гибких решений для преобразования дат в текстовые или числовые форматы: формулы ()ТЕКСТ), параметры форматирования, мощные надстройки, такие как Kutools, и встроенные функции, например Текст по столбцам. Каждый метод идеален для своего сценария: используйте формулы для динамических результатов, форматирование — чтобы быстро изменить внешний вид, Kutools — для удобной массовой обработки, 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек