Как часто мы получаем даты в электронных таблицах, предоставленных нам как 12.26.2016 или 26/12/2016 (формат для Великобритании), только для того, чтобы сказать, что дата недействительна или нет месяца 26? В этой статье рассматривается исправление дат с помощью VBA с использованием функций TRIM, LEFT, RIGHT и MID.
В статье предполагается, что у читателя отображается лента «Разработчик» и он знаком с редактором VBA. Если нет, погуглите «Excel Developer Tab» или «Excel Code Window».
Xlsm в этом упражнении можно скачать здесь.
Не наша проблема!
Лучшее место для решения проблемы — у ее источника. Однако никакие уговоры не смогут в данном случае убедить отдел заработной платы в том, что 12.26.1994 не является действительной датой (если только это не настроено в Панели управления компьютера для некоторых восточноевропейских стран).
На самом деле мы можем доказать, что он не является машиночитаемым. Например, добавление 7 дней к дате:
"=01.01.2017 + 7" = #VALUE. "=2017.01.01 + 7" = #VALUE.
тогда как…
"=2017-01-01 + 7" = 2017/01/08.
Предположим, они предполагают, что это не их проблема.
Форматы даты
Первое, что нам нужно выяснить, это дата в американском или международном формате.
Наш пример показывает, что мы рассматриваем использование в США, то есть MDY, а не международный формат DMY.
Как только мы установили источник, нам нужно изменить форматы данных, чтобы Excel мог их понять, будь то на международном уровне или в США.
Лучший способ сделать это — изменить дату на ггггммдд, формат, который не требует уточнения.
Процесс
Мы пройдемся по каждой строке документа, вызывая функцию для «корректировки» даты в соответствии со страной происхождения. Как только дата будет исправлена, мы рассчитаем возраст Сотрудника.
Кодекс
Скопируйте следующий код в новый модуль:
Option Explicit
Sub Main()
Dim strNewFormat As String
Dim strDate As String
Sheets("Main").Range("B4").Select
'Cycle through the sheet rows, using IDNumber as an anchor
'to prevent a premature halt caused by a blank date of birth
Do While ActiveCell > ""
If ActiveCell.Offset(0, 2) > "" Then
strDate = ActiveCell.Offset(0, 2)
'Remove leading or trailing spaces
strDate = Trim(strDate)
'Call the function
strNewFormat = ReformatDate(strDate, "USA")
'Write the result from the function ReformatDate to a new column
ActiveCell.Offset(0, 3) = strNewFormat
'Determine age by subtracting the previous column from today's date
ActiveCell.Offset(0, 4) = "=(NOW()-RC[-1])/365.25"
'Convert to intger, thus lopping off decimal places
ActiveCell.Offset(0, 4) = Int(ActiveCell.Offset(0, 4))
End If
Range("B" & ActiveCell.Row + 1).Select
Loop
End Sub
Function ReformatDate(sDate As String, sSource As String)
Dim yyyy, mm, dd As String
yyyy = Right(sDate, 4)
If sSource = "USA" Then
mm = Left(sDate, 2)
dd = Mid(sDate, 4, 2)
Else
mm = Mid(sDate, 4, 2)
dd = Left(sDate, 2)
End If
ReformatDate = yyyy & "-" & mm & "-" & dd
End Function
Добавьте кнопку в форму и назначьте ее Sub Main.
Предостережение
Прежде чем добавлять слишком много сложного кода в свой модуль, имейте в виду, что Excel не всегда стабилен при разработке крупных приложений и часто не может сам восстановить поврежденный код. Результатом может быть повреждение вашей единственной копии, поскольку повреждение происходит в «Сохранить».
Часто выполняйте резервное копирование и используйте инструмент для исправления Повреждение файла Excel.
Об авторе:
Феликс Хукер — эксперт по восстановлению данных в DataNumen, Inc., которая является мировым лидером в области технологий восстановления данных, включая ремонт rar повреждение файла и программные продукты для восстановления sql. Для получения дополнительной информации посетите www.datanumen.com