When it is useful?
Most of the time the most annoying problem is when the data is taken from ERP or other third-party software. It takes a lot of time to correct such dates. This macro will be very useful in such cases.
Step 1: Press the Shortcut keys Alt + F11 to open the Visual Basic for Application window.
Step 2: In the Visual Basic for Applications window, Click Insert > Module, then Copy the below VBA code into the code window.
[code lang=”vb”]
Sub CleanDates()
Dim iDateCol As Integer
Dim iStartRow As Integer
Dim iEndRow As Integer
Dim rngDates As Range
Dim isDDMMYY As Boolean
Dim iDate As Integer
Dim iMonth As Integer
Dim iYear As Integer
Dim strMonth As String
iDateCol = 5
iStartRow = 2
‘Teach excel what is the date format of the dates which are to be cleaned
isDDMMYY = True
With ActiveSheet
iEndRow = .UsedRange.Rows.Count
For Each rngDates In ActiveSheet.Range(Cells(iStartRow, iDateCol), Cells(iEndRow, iDateCol))
If VBA.IsNumeric(rngDates.Value2) = False Then
If isDDMMYY = True Then
strMonth = VBA.Format(VBA.DateSerial(1901, Mid(rngDates.Text, 4, 2), 1), “mmm”)
Else
strMonth = VBA.Format(VBA.DateSerial(1901, Left(rngDates.Text, 2), 1), “mmm”)
End If
rngDates.Value = Left(rngDates.Value, 3) & strMonth & Right(rngDates.Value, 5)
Else
iDate = VBA.Month(rngDates.Value)
iMonth = VBA.Day(rngDates.Value)
iYear = VBA.Year(rngDates.Value)
rngDates.Value = VBA.DateSerial(iYear, iMonth, iDate)
End If
rngDates.NumberFormat = “m/d/yyyy”
Next rngDates
End With
End Sub
[/code]
Step 3: Press the F5 key or click the Run button to run the code.
Excel VBA Macros for Non-Coders
Do you find VBA Macros scary? Did you miss any job opportunity because of it? Get started today with our eBook guide on using – Excel VBA Macros. 140 pages of rich visuals. Download now.