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

 

[Andrew Poulsom]

Welcome to MrExcel.

Have you tried Data|Text To Columns|Finish?

 

[Jonmo1]

Welcome to the board.

I'm going to guess that the issue is related to your windowsregional settings.
You probably have your regional date settings at mm/dd/yyyy
So when Excel sees a date in the format of 26-04-11, it considers it just a text string.

Can we confirm this?
Use = ISNUMBER(A1) and Fill down your list of dates
Which ones result in True and which False?

 

[5thMarReg]

You are correct about the dashed dates being recognized as a text string; they return FALSE.
So it's just a settings issue??
(kicking myself for not asking for help sooner)
Ok,so I'm on a Windows 7 laptop. Can you tell me how to alter the settings?

 

[Apocolyse]

well try to enter this formula next to ur column =text(text(A1,"#,##0")*1,"mm-dd-yyyy") and u will see all the date will come back to normal ^^. Hope this help. No need for VBA though

 

[5thMarReg]

btw, I found where I can make changes, but wouldn't that then make the other dates text strings?

 

[Jonmo1]

You are correct about the dashed dates being recognized as a text string; they return FALSE.
So it's just a settings issue??
(kicking myself for not asking for help sooner)
Ok,so I'm on a Windows 7 laptop. Can you tell me how to alter the settings?

I wouldn't say that you should "Change" your settings.

You want your reginal date settings to reflect YOU.
When YOU enter a date, do YOU enter it as mm/dd/yyyy or dd/mm/yyyy?
Whichever is the way you enter dates should be the one that is TRUE for ISNUMBER.

Because if you just change your settings, inevitably the opposite will happen,
and you'll recieve data that has the date in mm/dd/yyyy format, and 5/18/2012 will be read as text.

I'd suggest the Data - Text to Columns solution Andrew suggested.
Highlight the column,
Click Data - Text To Columns
Select Deliminated - Click Next
Click Next
Select Date MDY


The dates that are already dates will remain unaffected, but the text dates should get converted to real dates.

 

[5thMarReg]

Hmmm... When I apply that to the dashed dates, it returns #VALUE!
It works fine on the slashed dates of course, but then again, they aren't really my nemesis in this battle.

Btw, whether it's ultimately necessary or not, I'd really prefer to have a VBA solution.

 

[5thMarReg]

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

[TABLE="class: cms_table, width: 105"]
<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]

Into this....

[TABLE="width: 192"]
<colgroup><col style="width:48pt" span="3" width="64"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64, align: right"]26[/TD]
[TD="width: 64, align: right"]4[/TD]
[TD="width: 64, align: right"]11[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, align: right"]27[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]12/2/2011[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64"]8/8/2011[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, align: right"]15[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]11
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, align: right"]13[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]11[/TD]
[/TR]
</tbody>[/TABLE]

:/

 

[Apocolyse]

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