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.
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.