Adjust multiple date formats

**Armitage2k** · 08-09-2009, 08:30 AM

I have a column with date which was generated by my companies management system. however, this column now has two different date formats.

the majority is in mm.dd.yyyy format, some unfortunately are in dd-mm-yyyy format.

I have tried to set the date format for the whole column but does not change anything. is there any way to look for macros in the dd-mm-yyyy format and adjust them to mm.dd.yyyy?

Thanks,
A2k

**martindwilson** · 08-09-2009, 08:33 AM

is one or the other real dates?
if say
25/10/2009 is real (format cell general if it changes to a number its "real")
and 10.25.2009 is text
then select column
data/text to columns
click next
click next again
choose date and select the format of your text date in this case mdy
click finish
warning this may affect the real dates so its best to number your rows in a spare column 1 down to whatever .then sort all the worksheet by dates column so all real dates together and all text dates the same
copy text dates to a spare column do the above procedure then copy back over original
then resort data by your numbered column to get it all back in the right order/ delete numbered column

**Armitage2k** · 08-09-2009, 09:29 AM

Ahm... this is complicated :D

I have around 2000 rows and now when I did that, the other correct dates got affected when I was trying to copy them as part of my macro.

Is there any way to have a macro do that for me? I dont really want to start looking around for incorrect dates and then start formatting...

Thanks,
A2k

**martindwilson** · 08-09-2009, 09:32 AM

how long does a sort take?

**Armitage2k** · 08-09-2009, 11:06 AM

again, its not the sort. I have a problem afterwards when i copy these entries to another sheet. Right now I use search and replace to replace the "-" with "." but that just mixes me up the date and months. asides of this, I am not the one who has to do that all the times, I need some colleagues who are not "exactly" into excel do this on a daily basis. Simply, the easier, the better.

Thanks,
A2k