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

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?

MHT1974
Copper Contributor
Apr 02, 2025
Hi Hans, thx so much for your response and sorry for the delay, was on leave.
I have an existing Macro that copies invoice data from various files together into one file.
As you will see from the attached Demo file, sometimes the date comes across correctly and sometimes it doesn't.
The count of headers tell me how many files it came from and I usually ignore these.
- HansVogelaar
  MVP
  Apr 03, 2025
  I don't see an attached Demo file.
  - MHT1974
    Copper Contributor
    Apr 03, 2025
    Apologies, let me try again Hans.