Dates Problems

[hardeep.kanwar]

Hi! Experts


In column A i have Dates with different Format.


Like


Dates

28/08/09

05-09-2009

8.27.2009

8.27.2009

8.27.2009

8.29.2009

25/08/09

25th Aug

31.08.09

2nd sep

2nd sep

4th Sep


Now i want to Change this Dates of MM-DD-YYYY FORMAT.


When i change the Whole Columnn i.e. Column A into mm--MM-DD-YYYY FORMAT. Some Cell Apply this format some not.


Is it Possible to Change the Whole Column in a Once.


Or I have to go Manually. Column A has 1500 data.


Thanks in Advance


Hardeep kanwar

 

[keymaster]

Looks like some of the values in the column are not dates (as per excel)


I am not sure if this would work, but I will go ahead and test my ignorance.

in next column write the formula =datevalue(a1) and copy it down. Now use these values.

If you see any error there, you should manually format the values in a1 to be dates.


Another option is to write a date cleanup UDF / macro. but the possibilities are overwhelming, so I wouldnt bother with that unless there are millions of dates with non-recognizable formats.