VBA to convert MMM DD YYYY to date

Hi All,

I am looking for an Excel VBA code that would e.g. check in Col D if the date is already in date format and if not convert it from text to date format and repeat this for all populated cells in Col D.

e.g.

Col D

Date

23/03/2025

MAR 23 2025

Any help would be greatly appreciated,

Thx so much

excel

HansVogelaar

Apr 07, 2025

Thank you. Try this:

Sub Convert2Date()
    Dim rng As Range
    Dim cel As Range
    Application.ScreenUpdating = False
    On Error Resume Next
    Set rng = Range(Range("D2"), Range("D" & Rows.Count).End(xlUp))
    rng.NumberFormat = "m/d/yyyy"
    For Each cel In rng
        cel.Value = CDate(cel.Value)
    Next cel
    Application.ScreenUpdating = True
End Sub

HansVogelaar

MVP

Mar 24, 2025

Sub Test()
    Dim rng As Range
    Dim parts() As String
    Application.ScreenUpdating = False
    For Each rng In Range(Range("D2"), Range("D" & Rows.Count).End(xlUp))
        If Application.IsText(rng.Value) Then
            parts = Split(rng.Value)
            If UBound(parts) > 0 Then
                rng.Value = DateValue(parts(1) & "-" & parts(0) & "-" & parts(2))
            Else
                parts = Split(rng.Value, "/")
                If UBound(parts) > 0 Then
                    rng.Value = DateSerial(parts(2), parts(1), parts(0))
                End If
            End If
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

MHT1974
Copper Contributor
Mar 25, 2025
Good morning Hans, thx so much for the speedy response.
When testing this I get a Run Time Error '13', see below
And here's a snippet of what the spreadsheet Col D looked like before running the VBA.

Would appreciate any further advise pls
- HansVogelaar
  MVP
  Mar 26, 2025
  Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

Forum Discussion

VBA to convert MMM DD YYYY to date

Resources