Как преобразовать формат ДД.ММ.ГГГГ в формат даты (ММ/ДД/ГГГГ) в 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. Вы можете выбрать различные форматы отображения дат (Краткая дата, Полная дата и т. д.) из выпадающего списка Формат чисел на вкладке Главная в 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 для приложений нажмите Вставить > Модуль и вставьте следующий код в окно модуля:
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, чтобы запустить этот код, в появившемся окне выберите диапазон, содержащий ваши даты дд.мм.гггг, и нажмите OK.
Примечания и советы:
- Если возникла ошибка или ничего не произошло, проверьте свой выбор и убедитесь, что формат точно соответствует дд.мм.гггг.
- Вы можете настроить шаблон 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-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек