Excel VBA Code

VBA Code to Clean the Date Format

1 Mins read

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.

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

Step 3: Press the F5 key or click the Run button to run the code.