VBA Cdate to interpret DD-MM-YYYY

Question

I have a column of dates that are stored in the DD-MM-YYYY format. I'm looping through them with VBA's cdate function to convert them to values - however, while the day of the month is 12 or smaller, Cdate interprets the date as MM-DD-YYYY. What's the most convenient way around this?

If your regional settings don't use dd-mm-yyyy format, as appears to be the case, and if the dates are actually stored as text, you'll have to parse the date string yourself. — Rory
If the dates are actually formatted as dates in excel then there would be no reason to pass them to CDATE() to convert them. Just set your date variable to the cell value. If they are stored as text, then you have to parse the string with Mid() to rearrange it and pass the result to Cdate(). — JNevill
They arent, they're formatted as text (strings extracted from HTML and then removed tags) — Daniel Slätt
Split them on the / and feed them to the Dateserial function. Or, use the Data/Text-to-columns wizard and define the column format as DMY (or the equivalent in Power Query — Ron Rosenfeld

Nick Nick · Accepted Answer · 2018-11-02T12:56:13

I would use the DateSerial function.Try this:

Sub ConvertDate()
Dim datecell As Variant, i As Long

With Workbooks("book1").Sheets(1)
    For i = 1 To .Cells(Rows.Count, 1).End(xlUp).Row
        datecell = Split(.Range("A" & i), "/")
        .Range("B" & i) = DateSerial(datecell(2), datecell(1), datecell(0))
    Next i
End With

End Sub

This assumes your workbook is called "Book1" and the date values are in column A. The results will be written to column B.

VBA Cdate to interpret DD-MM-YYYY

1 Answers