Convert Date into workable excel date format

Originally Posted by sweep

=DATEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(A1,FIND(" ",A1)+1,LEN(A1)),"th",""),"rd",""),"st",""))

I was surprised that this works with August dates, because the "st" in August is also being removed, but apparently

=DATEVALUE("1 Augu 2014")

still works!

.....you do need to also SUBSTITUTE for "nd", though, otherwise you'll get an error with

Monday 2nd February 2015