Problem with Date Conversion Code

J

JOUIOUI

I have the below code that is part of an existing macro to change my date in
Column C from this odd format of

Y/MM/DD to MM/DD/YYYY

however it is not calculating correctly. Here are a few examples of the
coversion after the macro is run

"60526" converted to "26/05/2006"
"60523" converted to "23/05/2006"

I need to change where the month is to where the day is and where the day is
to the month.

With Selection

Dim RowNum As Integer
Dim NextValue As String
Dim NextDate As Date

For RowNum = 2 To Range("C1").CurrentRegion.Rows.Count
NextValue = Range("C" & RowNum).Value
NextDate = DateSerial(2000 + Mid(NextValue, 1, 1), Mid(NextValue, 2,
2), Mid(NextValue, 4, 2))
Range("C" & RowNum).NumberFormat = "dd/mm/yyyy"
Range("C" & RowNum).Value = NextDate
Next RowNum

End With
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top