Converting different date formats

[5thMarReg]

I received a file with a date field that contains values such as...

[TABLE="width: 105"]
<colgroup><col></colgroup><tbody>[TR]
[TD]26-04-11
[/TD]
[/TR]
[TR]
[TD]27-08-11[/TD]
[/TR]
[TR]
[TD="align: right"]12/2/2011
[/TD]
[/TR]
[TR]
[TD="align: right"]8/8/2011[/TD]
[/TR]
[TR]
[TD]15-03-11
[/TD]
[/TR]
[TR]
[TD]13-02-11
[/TD]
[/TR]
</tbody>[/TABLE]

I need a VBA solution that will convert these values so that they can be sorted (and displayed) as recognizable dates.
I tried to come up with something, but the code got so convoluted and unwieldy that all it really does is just confuse me to look at it now. (and no, I haven't been able to get it to work)

I obviously need something that is concise and effective (as opposed to something convoluted and confusing).
Can anyone help me work this out?
Thanks

 

[5thMarReg]

Try

Sub test()
For Each c In Range("A1:A10")
    If IsDate(c.Value) Then
        c.Value = DateValue(c.Value)
    End If
Next c
End Sub

Very frustrating....

[TABLE="width: 94"]
<colgroup><col></colgroup><tbody>[TR]
[TD]BEFORE
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Date
[/TD]
[/TR]
[TR]
[TD]17-12-11[/TD]
[/TR]
[TR]
[TD]17-12-11[/TD]
[/TR]
[TR]
[TD="align: right"]7/12/2011
[/TD]
[/TR]
[TR]
[TD="align: right"]1/12/2011
[/TD]
[/TR]
[TR]
[TD]29-11-11
[/TD]
[/TR]
[TR]
[TD]26-11-11[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]AFTER
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[/TR]
[TR]
[TD="align: right"]12/11/2017[/TD]
[/TR]
[TR]
[TD="align: right"]12/11/2017[/TD]
[/TR]
[TR]
[TD="align: right"]7/12/2011[/TD]
[/TR]
[TR]
[TD="align: right"]1/12/2011
[/TD]
[/TR]
[TR]
[TD="align: right"]11/11/2029
[/TD]
[/TR]
[TR]
[TD="align: right"]11/11/2026
[/TD]
[/TR]
</tbody>[/TABLE]

Since I can't attach the file itself (or can I??) can someone just copy the "BEFORE" dates and try whatever method that they want to try in their version of Excel?

 

[MARK858]

Since I can't attach the file itself (or can I??) can someone just copy the "BEFORE" dates and try whatever method that they want to try in their version of Excel

I did with the code I posted I got
[TABLE="width: 101"]
<tbody>[TR]
[TD="align: right"]27/08/2011[/TD]
[/TR]
[TR]
[TD="align: right"]12/02/2011[/TD]
[/TR]
[TR]
[TD="align: right"]08/08/2011[/TD]
[/TR]
[TR]
[TD="align: right"]15/03/2011[/TD]
[/TR]
[TR]
[TD="align: right"]13/02/2011[/TD]
[/TR]
</tbody>[/TABLE]

To show your workbook. Upload your workbook to a file hosting site like Box.com, mark the file for sharing and post a link in the thread.

 

[5thMarReg]

I did with the code I posted I got
[TABLE="width: 101"]
<tbody>[TR]
[TD="align: right"]27/08/2011[/TD]
[/TR]
[TR]
[TD="align: right"]12/02/2011[/TD]
[/TR]
[TR]
[TD="align: right"]08/08/2011[/TD]
[/TR]
[TR]
[TD="align: right"]15/03/2011[/TD]
[/TR]
[TR]
[TD="align: right"]13/02/2011[/TD]
[/TR]
</tbody>[/TABLE]

To show your workbook. Upload your workbook to a file hosting site like Box.com, mark the file for sharing and post a link in the thread.

Those aren't valid dates though. Or I should say they aren't MM/DD/YYYY dates, so they don't match the other dates that already had the slashes.

 

[5thMarReg]

Ok, I resolved it in VBA.
For those who may be interested, here it is (admittedly not elegant, but it works)....

Range("A1").Select
For x = 1 To 20
ActiveCell.Offset(1, 0).Activate

thatDATE = ActiveCell.Value
fullDATE = Len(thatDATE)
dashPOS = InStr(thatDATE, "-")

If dashPOS > 0 Then
monthDATE = Left(thatDATE, dashPOS - 1)
thatDATE = Right(thatDATE, fullDATE - dashPOS)
dashPOS = InStr(thatDATE, "-")
dayDATE = Left(thatDATE, dashPOS - 1)
yearDATE = Right(thatDATE, 2)
newDATE = dayDATE & "/" & monthDATE & "/20" & yearDATE

ActiveCell.Value = newDATE

End If

Next x

Thanks to all who who took time out of their day to assist me!

 

[Jonmo1]

As long as you got it resolved, that's what matters.