Билл сталкивается с проблемой импорта данных в Excel, которые изначально были созданы в других приложениях. Проблема в том, что данные содержат много дат, но имеют формат, который Excel не понимает. Например, даты могут быть в формате 09.15.20 или 9.15.2020, ни один из которых не обрабатывается Excel как дата. Билл хочет знать, как преобразовать нестандартные даты в формат даты, понятный Excel.

Если даты указаны в том же формате последовательности, который вы используете в своих региональных настройках, преобразование не составит труда. Например, если в ваших региональных настройках используется формат даты MDY (месяц, за которым следует день, за которым следует год), а дата, которую вы импортируете, имеет тот же формат, тогда вы можете просто выбрать ячейки и заменить точки косой чертой. Когда Excel изменяет 9.15.2020 на 15.09.2020, он автоматически анализирует результат как дату.

Если формат, который вы импортируете, не соответствует вашим региональным настройкам, вам нужно перетасовать дату в том же формате. Например, если дата, которую вы импортируете, — 09.10.20 (10 сентября 2020 г.), и ваша система будет интерпретировать это как 9 октября 2020 г., то самый простой способ — разделить дату на отдельные компоненты, а затем вернуть их. все вместе. Выполните следующие общие шаги:

  1. Вставьте три пустых столбца справа от столбца даты.

  2. Выделите ячейки, содержащие нестандартные даты.

  3. С помощью инструмента «Текст в столбцы» (доступного в группе «Инструменты для работы с данными» на вкладке «Данные» на ленте) выберите данные с разделителями и используйте точку в качестве разделителя. По завершении работы мастера вы получите три столбца, содержащие месяц, день и год.

  4. В оставшемся пустом столбце введите следующую формулу:

  5. Скопируйте формулу в другие ячейки рядом с датами.

  6. Выделите ячейки, содержащие только что созданные формулы, затем нажмите Ctrl + C.

  7. Используйте Специальную вставку, чтобы преобразовать формулы в результаты. (Выберите параметр «Значения» при использовании специальной вставки.)

  8. Удалите три столбца, содержащие разделенные даты, и оставьте столбец, содержащий окончательные даты.

Другое решение — просто использовать макрос для преобразования. Ниже приведена определяемая пользователем функция, которая принимает нестандартную дату и преобразует ее в правильно отформатированное значение даты. Макрос также переключает положение месяца и дня, как это сделано в методе Text to Columns.

Public Function Convert_Date(A As String) As Date     Dim K As Long     Dim K1 As Long     Dim K2 As Long

K = Len(A)

K1 = InStr(1, A, ".")

K2 = InStr(K1 + 1, A, ".")

Convert_Date = DateSerial(Val(Mid(A, K2 + 1, _       K - K2 + 1)), Val(Mid(A, K1 + 1, K2 - K1)), _       Val(Mid(A, 1, K1 - 1)))

End Function

_Примечание: _

Если вы хотите узнать, как использовать макросы, описанные на этой странице (или на любой другой странице на сайтах ExcelTips), я подготовил специальную страницу, содержащую полезную информацию.

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (9837) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365. Вы можете найти версию этого совета для старого интерфейса меню Excel здесь:

link: / excel-Parsing_Non-Standard_Date_Formats [Анализ нестандартных форматов даты].