Forum Discussion

MHT1974

Copper Contributor

Mar 24, 2025

Solved

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

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

MHT1974

Copper Contributor

Apr 07, 2025

hi hans, hope you are well.
i did both the attachment and the screenshot, but maybe the upload get's blocked by our IT.
here's a sample block of the full file below. hope that helps with the dates in Col D would need to be converted pls if they aren't in date format or ignored if they stem from other headers.

DOCUMENT TYPE	INVOICE NUMBER	CURRENCY	BILLING CYCLE DATE
xx	xx	EUR	10-Dec-24
xx	xx	EUR	AUG 14 2024
xx	xx	EUR	AUG 14 2024
xx	xx	EUR	AUG 14 2024
xx	xx	EUR	AUG 14 2024
xx	xx	EUR	BILLING CYCLE DATE
xx	xx	EUR	BILLING CYCLE DATE
xx	xx	EUR	JUL 29 2024
xx	xx	EUR	JUL 29 2024

HansVogelaar

MVP

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

MHT1974
Copper Contributor
Apr 09, 2025
This works a treat now, thx so much Hans for all the help - much appreciated.
All the Best, Markus