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]

hi there, is it the result of trying my formula?

Sorry, I guess I should be replying with quotes in order to avoid confusion about my responses to different suggestions.

Anyway, I did try that formula, and the dashed dates returned a #VALUE! result.

 

[Jonmo1]

Uncheck ALL the options in the Deliminated Options page.

I actually had tried text to columns.
It turned this...

26-04-11

27-08-11

12/2/2011

8/8/2011

15-03-11

13-02-11

<TBODY>
</TBODY>

Into this....

26	4	11
27	8	11
12/2/2011
8/8/2011
15	3	11
13	2	11

<TBODY>
</TBODY>

:/

 

[Apocolyse]

If Jonmo1 's solution still doesn't work like u want, can u provide us the file?

 

[5thMarReg]

Uncheck ALL the options in the Deliminated Options page.

Unchecking results in NO delimited columns. Nothing happens at all.

I really did want to accomplish this with VBA, so I may just have to go back to struggling with my VBA code. I can find the location of the dashes and separate parts of the values, but I wasn't "feeling the logic", so it was a struggle. Maybe if I start all over again with a fresh head it will result in a solution.

Thanks anyway to all who have offered suggestions to me with with this

 

[5thMarReg]

If Jonmo1 's solution still doesn't work like u want, can u provide us the file?

I don't see anything here where it will allow me to attach a file.

 

[Jonmo1]

Don't worry so much about VBA solution yet.
Get a working solution (period) first.
THEN once you have a working solution, the Macro Recorder is your friend.


That said, make sure you're following these steps.

Select the whole column of dates
Data - Text To Columns
Select Deliminated - Click Next
Uncheck All Delimiter options - Click Next
Select Date - DMY <--This is the critical part
Click Finish.

 

[MARK858]

If you insist on VBA try the code below. It is just a VBA version of what has already been suggested. Change the column to suit.

Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 4), TrailingMinusNumbers:=True

Edit: Apologies to JonMo1 I posted the code before I saw the last post

 

[5thMarReg]

If you insist on VBA try the code below. It is just a VBA version of what has already been suggested. Change the column to suit.

Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 4), TrailingMinusNumbers:=True

I've tried text to columns exactly as detailed by Jonmo1 and I executed the code that you provided.
NO result. No new columns. Nothing.

I'd be more than happy to attach a copy of the file, but I don't see where that options exists.
(sorry, but I'm a noob here)

Btw, I'm writing an extensive VBA solution that encompasses multiple procedures that are applied to the worksheet and none of them require altering the structure of the workshhet (no added cells, columns, rows, worksheets) so while I'm not completely opposed to a text to columns solution, I'd prefer to keep everything within VBA. In other words, test the cell, alter the contents of the cell if it contains dashes, replace the original contents of the cell with a true date format.

No VBA guys/girls here?

 

[MARK858]

No new columns.

Text to columns when used this way does not produce extra columns. It changes underlying cell formats.

 

[Jonmo1]

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