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

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

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

  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.

Этот совет (3191) применим к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и более поздних версий) здесь:

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