Как преобразовать дату в числовую строку или текстовый формат в 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, которая может гибко указать выходной формат на основе ваших потребностей и региональных настроек. Этот метод подходит для преобразования любого формата даты в читаемую текстовую строку и широко используется для экспорта данных, подготовки сводок или быстрой стандартизации отображения дат.
Формула: =TEXT(дата,"формат_даты")
Ссылка: дата: ячейка с датой, которую вы хотите преобразовать в текст
формат_даты: формат, в котором вы хотите отобразить дату после преобразования в текст
Ниже приведены конкретные примеры, демонстрирующие, как эффективно использовать эту формулу для различных требований к отображению. Обратите внимание, что после использования формулы TEXT результат всегда возвращается в виде текстовой строки (не числа), поэтому она подходит для случаев, когда вам не нужно выполнять дальнейшие вычисления с датами.
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 Преобразование даты в текст в других форматах
Функция TEXT поддерживает широкий спектр форматов. Просто измените код формата в формуле, чтобы настроить вывод. Это полезно для подготовки различных строк дат, специфичных для регионов, для генерации документов или для использования в других программных продуктах, которые требуют фиксированные форматы дат. Примеры:
| Дата(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. Нажмите OK. Дата в выбранной ячейке преобразуется в 5-значное число, которое является внутренним способом хранения дат в Excel. Вы можете использовать это для вычислений, таких как вычитание дат для определения разницы в днях, или для системных интеграций, зависящих от серийных номеров дат.
Примечание: Если вам нужно вернуть число обратно в стандартный формат даты, просто следуйте тем же шагам и выберите категорию Дата.
2.2 Преобразование даты в число в формате ммддгггг или ддммгггг
Когда вы хотите преобразовать дату в числовую строку, отформатированную как ммддгггг или ддммгггг (что технически всё ещё текстовая строка в Excel, но только с цифрами), используйте пользовательский числовой формат. Это полезно для создания унифицированных числовых кодов дат для сортировки, импорта/экспорта или стандартизированных идентификаторов документов.
1. Щелкните правой кнопкой мыши ячейку, содержащую дату, которую вы хотите преобразовать, и выберите Формат ячеек из контекстного меню.
2. В диалоговом окне Формат ячеек на вкладке Число выберите Пользовательский в списке Категория . Затем введите нужный код формата, например ммддгггг, в поле Тип .
3. Нажмите OK. Теперь дата отображается как числовая строка в выбранном формате.
Вот некоторые другие часто используемые пользовательские коды и их результат:
| Дата | 2/23/2019 |
| Формат ячейки как пользовательский | ддммгггг | ддммгг | ммгггг | ггггммдд |
| Отображение | 23022019 | 230219 | 022019 | 20190223 |
Будьте осторожны: хотя число отображается визуально, Excel все равно воспринимает его как дату в ячейке, если вы не используете формулу TEXT для преобразования. Если вы экспортируете или используете данные в другой системе, проверьте, читается ли базовое значение ячейки или отображаемый текст.
Если вы хотите быстро преобразовать значения дат в месяц, день, год или любой другой пользовательский формат даты — даже для пакетных операций — инструмент Apply Date Formatting в Kutools for Excel может быть очень удобным. Это полезно для пользователей, которые предпочитают избегать сложных формул или повторяющихся действий правой кнопкой мыши, особенно для форматирования большого диапазона дат в разных стилях.
После бесплатной установки Kutools for Excel следуйте этим шагам:
1. Выберите ячейки, содержащие даты, которые нужно преобразовать.
2. Нажмите Kutools > Формат > Apply Date Formatting.
3. В диалоговом окне Apply Date Formatting выберите желаемый вывод из панели Форматирование даты . Вы можете немедленно просмотреть результат в правой панели Предварительный просмотр .
4. Нажмите OK для применения изменений, и все выбранные даты будут мгновенно переформатированы.
Советы: Этот метод особенно полезен для пакетной обработки и предоставляет визуальный предпросмотр, устраняя проб и ошибок. Если вы столкнетесь с неожиданными результатами, проверьте исходные данные на наличие недействительных записей дат или текстовых форматов дат, поскольку они могут не распознаваться инструментом должным образом.
Больше советов по преобразованию дат
Встроенная функция Excel «Текст в столбцы» также может использоваться для пакетного преобразования значений дат в текст или другие представления даты/числа, такие как при очистке импортированных данных с несовместимыми форматами или подготовке к миграции данных в другие системы.
Преимущество этого метода заключается в том, что он не требует формул или VBA и может обрабатывать несколько ячеек или столбцов одновременно. Однако он может перезаписать содержимое столбцов справа, поэтому всегда убедитесь, что в этих столбцах нет ценных данных, или выполните преобразование в новой области.
Шаги:
- Выберите диапазон, содержащий даты, которые вы хотите преобразовать.
- Перейдите на вкладку Данные на ленте и нажмите «Текст в столбцы».
- В мастере выберите Разделители и нажмите Далее (не выбирайте никакие разделители на шаге 2 мастера).
- Нажмите Далее снова, затем в разделе Формат данных столбца выберите Дата и укажите правильный входящий формат даты (например, MDY, DMY, YMD) или выберите Текст для принудительного преобразования в текстовый формат.
- Выберите ячейку назначения для вывода, если вы не хотите перезаписывать исходный столбец.
- Нажмите Готово.
После завершения выбранные значения дат будут преобразованы в выбранный формат (текст). Этот метод особенно эффективен для быстрого применения текстового форматирования к строкам дат или решения проблем с импортируемыми CSV/TXT файлами, где Excel может некорректно авто-конвертировать текстовые даты.
Предупреждение: Если выходные столбцы содержат формулы или важные данные, переместите или сделайте резервную копию данных заранее. Для неанглийских дат или пользовательских форматов дат обязательно проверьте выпадающий список «Дата», чтобы он соответствовал расположению исходных данных. Если возникают неожиданные результаты — например, если даты отображаются как числа или не конвертируются — дважды проверьте, что системный региональный формат даты соответствует вашему выбору в мастере.
Подводя итог, Excel предлагает множество гибких решений для преобразования дат в текстовые или числовые форматы, включая формулы (TEXT), параметры форматирования, мощные надстройки, такие как 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-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек