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?
1 Answers
0
votes
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.
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 withMid()
to rearrange it and pass the result to Cdate(). – JNevill/
and feed them to theDateserial
function. Or, use theData/Text-to-columns
wizard and define the column format asDMY
(or the equivalent in Power Query – Ron Rosenfeld