Convert text to date

[JJones_hay]

Guys, I have some horrible extract data with differing date formats that I need to convert to something consistent that I can format as a date.

This is an example of data.... all with general format at the moment.

2/28/2013 2:48:53 PM

1/16/2013 10:48:50 AM

12/17/2012 11:59:49 AM

<tbody>
</tbody>

I have used this formula to extract the date portion, but I can't get this to then format as date. How do I convert this to the julian date, so I can then apply a date format?

=LEFT(G9,SEARCH("/",G9)+7)

(The day portion of this date always has a leading zero)

Thanks

 

[Andrew Poulsom]

Just another possibility:

=SUBSTITUTE(REPLACE(A1,1,FIND("/",A1),""),"/","/"&LEFT(A1,FIND("/",A1)))+0

 

[Firefly2012]

And another (which was close to the idea I initially had when attempting a formula but which I discarded for some unknown reason - probably forgetfulness/age!):

=MID(REPT(LEFT(A2,FIND(" ",A2)-1)&"/",2),7-(MID(A2,2,1)="/"),FIND(" ",A2)-1)+0