Как преобразовать дату из формата ДД.ММ.ГГГГ в формат ММ/ДД/ГГГГ в Excel?
При работе с Excel вы можете столкнуться с датами, введёнными в формате дд.мм.гггг, из-за различных региональных привычек или личных предпочтений. Однако Excel автоматически не распознаёт формат дд.мм.гггг(например,)23.02.2024) как допустимый формат даты, что может вызвать проблемы при сортировке, фильтрации или вычислениях. Чтобы обеспечить полную совместимость и удобную обработку данных, важно преобразовать такие текстовые строки с датами в стандартный формат дат Excel — например, мм/дд/гггг.
Ниже представлены несколько эффективных решений для выполнения этой задачи — от формул и встроенных инструментов Excel до кода VBA. Каждый метод сопровождается пошаговыми инструкциями, полезными предостережениями и рекомендациями по устранению типичных проблем.
Преобразование дд.мм.гггг в дд/мм/гггг с помощью формулы
Преобразование мм.дд.гггг в мм/дд/гггг с помощью Kutools для Excel
Преобразование дд.мм.гггг в мм/дд/гггг с помощью формулы
Преобразование дд.мм.гггг в стандартную дату с помощью макроса VBA
Преобразование дд.мм.гггг с помощью функции «Текст по столбцам» (встроенная функция Excel)
Преобразование ДД.ММ.ГГГГ в ДД/ММ/ГГГГ с помощью формулы
Иногда достаточно заменить точки в формате дд.мм.гггг на косые черты, чтобы получить формат дд/мм/гггг. Это особенно полезно, когда разделитель должен соответствовать региональным настройкам, но имейте в виду: Excel может по-прежнему воспринимать результат как текстовую строку, а не как настоящую дату.
Чтобы выполнить это преобразование:
1. Предположим, исходная дата находится в ячейке A6. Выберите пустую ячейку рядом с ней — например, B6 — и введите следующую формулу:
=SUBSTITUTE(A6,".","/")2. Нажмите Enter, а затем перетащите маркер заполнения вниз, чтобы применить формулу к другим датам по мере необходимости.
Совет: В этой формуле A6 ссылается на ячейку с исходной датой. При необходимости скорректируйте ссылку на ячейку в соответствии с вашим диапазоном данных.
Хотя этот метод прост, помните: результат остаётся текстом, а не распознаваемым значением даты. Если для последующих операций — таких как вычисления, фильтрация и т.д. — требуются настоящие даты, воспользуйтесь приведёнными ниже решениями с формулами и VBA.
Преобразование ММ.ДД.ГГГГ в ММ/ДД/ГГГГ с помощью Kutools для Excel
Для дат в формате мм.дд.гггг инструмент Kutools для Excel предлагает практичную функцию под названием Распознавание даты. С её помощью вы сможете мгновенно преобразовать множество нестандартных значений даты в стандартный формат — особенно полезно, если вы часто работаете с импортированными или объединёнными данными из разных источников.
После бесплатной загрузки и установкиKutools для Excelвыполните следующие действия:
1.Выделите ячейки с датами, которые нужно преобразовать, и перейдите в меню Kutools > Содержимое > Распознавание даты.
2.Выделенные ячейки автоматически преобразуются в корректные значения дат Excel. Вы можете выбрать различные форматы отображения даты («Краткая дата», «Полная дата» и т.д.) в раскрывающемся списке «Числовой формат» на вкладке «Главная» для улучшенной визуализации.
Совет: если значение не распознаётся как допустимая дата, исходные данные останутся без изменений — это поможет избежать случайной потери информации.
Этот метод особенно эффективен для больших диапазонов и гарантирует, что результат будет содержать настоящие даты, готовые к немедленному использованию в вычислениях и фильтрации. Его преимущества — массовая обработка и простота преобразования, а возможный недостаток — необходимость установки надстройки Kutools.
Преобразование ДД.ММ.ГГГГ в ММ/ДД/ГГГГ с помощью формулы
Чтобы дополнительно преобразовать даты из формата дд.мм.гггг в стандартный формат мм/дд/гггг и обеспечить распознавание результата Excel как настоящей даты, используйте следующую формулу. Этот метод подходит, если ваши региональные настройки формата даты не распознают результат с косыми чертами, полученный простой функцией ПОДСТАВИТЬ, как дату.
1. Допустим, исходная дата находится в ячейке A6. В соседней ячейке, например B6, введите следующую формулу:
=(MID(A6,4,2)&,"/"&,LEFT(A6,2)&,"/"&,RIGHT(A6,2))+02. Нажмите Enter, а затем, если нужно, перетащите формулу вниз.
3. Результаты изначально могут отображаться как порядковые номера (например, 45457). Чтобы преобразовать их в даты, выделите эти ячейки, перейдите на вкладку Главная → Числовой формат и выберите вариант Краткая дата.
Теперь ваш текст в формате дд.мм.гггг преобразован в даты, распознаваемые Excel, в формате мм/дд/гггг.
Советы: чтобы скопировать формулу на несколько строк вниз, выделите первую ячейку с формулой, скопируйте (Ctrl+C), затем выделите остальные целевые ячейки и вставьте (Ctrl+V)
Код VBA – преобразование строк дд.мм.гггг в настоящие значения даты в диапазоне
Для опытных пользователей или тех, кто работает с большим объёмом данных в формате «Пользовательский», автоматизация преобразования с помощью макроса VBA может значительно сэкономить время и повысить эффективность. Этот метод напрямую преобразует текстовые даты в формате дд.мм.гггг в настоящие даты Excel в выбранном диапазоне.
Преимущества включают пакетную обработку и гибкость при выборе любого столбца или диапазона. Однако будьте внимательны: макросы VBA нельзя отменить с помощью Ctrl+Z. Обязательно создайте резервную копию данных перед запуском кода.
1. Щёлкните Инструменты разработчика > Visual Basic. В окне Microsoft Visual Basic for Applications выберите пункт Вставка > Модуль и вставьте следующий код в окно модуля:
Sub ConvertDDMMYYYYDotToDate()
Dim cell As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
For Each cell In WorkRng
If cell.Value Like "??.??.????" Then
cell.Value = DateSerial(Right(cell.Value, 4), Mid(cell.Value, 4, 2), Left(cell.Value, 2))
cell.NumberFormat = "mm/dd/yyyy"
End If
Next
Application.ScreenUpdating = True
End Sub2. Затем нажмите клавишу F5, чтобы запустить этот код. В появившемся диалоговом окне выделите диапазон, содержащий ваши даты в формате дд.мм.гггг, и нажмите кнопку ОК.
Примечания и советы:
- Если возникает ошибка или ничего не происходит, проверьте выделение и убедитесь, что формат даты точно соответствует шаблону дд.мм.гггг.
- Вы можете изменить шаблон cell.Value Like «??.??.????», если в ваших данных длина цифр варьируется.
- Этот макрос невозможно легко отменить — всегда сначала сохраняйте резервную копию своих данных.
- Преобразованные ячейки Excel сразу распознает как настоящие даты.
Это решение на основе VBA идеально подходит для пользователей, уверенно владеющих базовыми операциями с макросами и нуждающихся в быстром, точном и воспроизводимом преобразовании больших объёмов данных.
Другие встроенные методы Excel – использование функции «Текст по столбцам»
Еще один практичный способ — использовать встроенную функцию Excel «Текст по столбцам». Этот метод идеально подходит, когда данные с датами однородны и расположены в одном столбце.
1.Выделите столбец или ячейки с датами в формате дд.мм.гггг.
2. Перейдите на вкладку Данные → Текст по столбцам.
3. В мастере выберите параметр С разделителями, затем нажмите кнопку Далее.
4. Установите флажок только Другое, чтобы указать разделители, и введите точку ().) в поле.
5. Нажмите Далее. На следующем шаге установите Формат данных столбца для столбцов День, Месяц и Год как Общий или Текст в зависимости от ситуации.
6. Завершите работу мастера, чтобы разбить данные на три столбца: День, Месяц и Год.
7.В новом столбце объедините день, месяц и год в дату с помощью формулы:
=DATE(C1, B1, A1)Предположим, что столбцы A, B и C теперь содержат День, Месяц и Год соответственно. Примените формулу и, при необходимости, протяните её вниз.
Эти решения предлагают гибкие способы преобразования формата дд.мм.гггг и аналогичных форматов даты в даты, распознаваемые 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек