Converting different date formats

5thMarReg

New Member
Joined
Mar 23, 2013
Messages
13
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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?
 
Upvote 0
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?
 
Upvote 0
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 :)
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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....

26411
27811
12/2/2011
8/8/2011
15311
13211

<colgroup><col style="width:48pt" span="3" width="64"> </colgroup><tbody>
</tbody>

:/
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top