How to fix the dates and numbers in this workbook

I had this question after viewing how to convert m/d/yyyy to d/m/yyyy.

please see attached example dummy file, i need a macro to to clean up the messed data input.

there are two worksheets in the attached workbook.  Sheet called "messedup" is what i have right now.
the sheet "Cleaned" is the desired solution that after i run the macro it should give me that.

any help is appreciated.
Who is Participating?
pls try
Sub macro1()

For Each c In Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
    aDate = Split(c, "/")
    c.Value = DateSerial(aDate(2), aDate(0), aDate(1))
    c.Offset(, 4) = Replace(Replace(c.Offset(, 4), ".", ""), ",", ".")
    c.Offset(, 5) = Replace(Replace(c.Offset(, 5), ".", ""), ",", ".")
    c.Offset(, 4).Resize(1, 2).NumberFormat = "#,##0.00"
    c.Offset(, 6).NumberFormat = "dd/MM/yyyy"
    If c.Offset(, 6) Like "*/*" Then
        c.Offset(, 6) = DateValue(c.Offset(, 6))
    End If
End Sub

Open in new window

Brian BEE Topic Advisor, Independant Technology ProfessionalCommented:
You have two issues, one is some of the dates are actually text. That can be fixed by the formula from your previous question.

The other is there are date serial number that need to be formatted properly. Just format the column as a date. That will take care of those cells.
FloraAuthor Commented:

thanks a million.

another piece of magical code!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.