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