Help with Date Macros - eg. Change Values From Jun 5, 2011 to 05/06/2011

Hi everyone I'm new here and need help in developing a macro to change the dates of various fields in one column.

I need to change the date format of Jun 5, 2011 to 05/06/2011

I have tried the inbuilt functions of highlighting the required cells and clicking 'Format Cells-->Category:Date--->Type3rd selection)' however this wont work.

I may also have other date formats within this column so I guess I might have to create an IF statement saying IF date = Jun 5, 2011 then 05/06/2011 else 05/06/2011.

Can anyone help me with this conundrum please?

Regards


Red

Ok we got half way there in formula world using:

=CONCATENATE(MID($R12,5,SEARCH(",",$R12)-5), " ",SUBSTITUTE($R12,CONCATENATE(MID($R12,5,SEARCH(",",$R12)-5 ),","),""))

Which gave us this result:5 Jun 2011

(Imagine R12 is Jun 5, 2011)


Almost there but it added an extra space...

eg. 5*Jun**2011

What I want to do is get rid of this space then convert this into a macro

Ok as requested - attached is a spreadsheet with clear specifications as to what I'm trying to achieve - the formula posted above is present to illustrate what I need the Macro to do

Hope this clarifies things better and that someone can lend a helping hand


Thanks guys


Red

Date Standardisation macro Requirements.xls

Select the dates. Then choose Data-Text to columns, specify delimited in step 1, accept defaults for step 2, then in step 3 specify Date and MDY format, then click Finish.

Curious. Oh, did you have Comma checked as a delimiter in step 2 of the wizard? If so, uncheck it.