Converting different date formats

[5thMarReg]

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

26-04-11

27-08-11

12/2/2011

8/8/2011

15-03-11

13-02-11

<colgroup><col></colgroup><tbody>
</tbody>

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....

BEFORE

Date

17-12-11

17-12-11

7/12/2011

1/12/2011

29-11-11

26-11-11

AFTER

Date

12/11/2017

12/11/2017

7/12/2011

1/12/2011

11/11/2029

11/11/2026

<colgroup><col></colgroup><tbody>
</tbody>

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

27/08/2011

12/02/2011

08/08/2011

15/03/2011

13/02/2011

<tbody>
</tbody>

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

27/08/2011

12/02/2011

08/08/2011

15/03/2011

13/02/2011

<tbody>
</tbody>

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.