2022 updates MS Excel - Data Cleaning Course Enroll now  
Excel VBA CodeTutorials

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

Related posts

Alteryx Hotkeys - Alteryx Keyboard Shortcuts

2 Mins read
Top 50+ Alteryx Shortcuts for Windows. Alteryx is popularly known as a Self-Service Analytics tool. Business users can build their data workflows…

Step By Step Guide to Learn Alteryx

6 Mins read
Alteryx Learning Path: The growth in technology has resulted in growth in understanding. In today’s world, humans – fellow businessmen know the…
Excel VBA CodeTutorials

VBA Code to Split table by Columns

3 Mins read
Step 1: Press the Shortcut keys Alt + F11 to open the Visual Basic for Application window. Step 2: In the Visual…