Text to Date, Convert to US Format from UK, Eliminate Time

Good Morning,

Yes another "date" thread . I have been working on this problem for several hours and haven't been able to resolve. I also searched the forum and I see members using similar formulas, but it's not working for me.

I have Date and Time data in text (as it's right aligned) in my cell..it's in UK format:
1/9/2013 12:00:00 AM

I want my output to be 9/1/2013.

I use "=DATE(MID(A1,5,4),MID(A1,3,1),MID(A1,1,1))"

My result is "2/4/1903"? Is it because time is in the cell too so it's throwing it off?

The other thing is my dates will also have double digits, so I could have 10/9/2013 and I would want output 9/10/2013. My above formula would need tweaking for double digits.

Any assistance is MOST DEFINITELY appreciated at this point...at my wit's end with this.



EDIT - SAMPLE FILE
SAMPLE.xlsx

Select the column with the date/times, goto Data>Text to columns..., select Fixed Width and click Next.

On the next step make sure the date and time are in separate columns and click Next.

On the 3rd step select the column with the date and set it's Column data format to MDY, select the column with time and choose Do not import.

Click finish.

You should be left with 'real' dates that can be formatted as you want.

Actually before you try that, check that what you have isn't already a 'real' date.

That can be done with ISNUMBER.

Why not copy the original data to where you wanted to output it to?

Then do the text to columns.

Can you upload an example workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

When I carried out the steps I described for text to columns it worked for me.

The only problem was some of the dates needed reformatting.

Is that not the date you expect?

Did you try reformatting the dates like that?

By the way, what did you choose for the date format on the 3rd step of text to columns?

No matter what your regional settings it should be DMY.

Like I said the only problem was some dates need reformatting.