Convert Date into workable excel date format

if you want it to be, such as dd/mm/yyyy

=TEXT(A1,"dd/mm/yyyy")

To convert to an excel date,

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

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

Originally Posted by daddylonglegs

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

Thanks for the "nd" correction - I had considered the "August issue" and was too surprised when it worked.

Assuming your current data is text and not a formatted date, then with data in A2 try this formula in B2

=MID(REPLACE(A2,FIND(" ",A2,FIND(" ",A2)+1)-2,2,""),FIND(" ",A2)+1,99)+0

Format B2 in required date format

This formula also works for me

=MID(REPLACE(F2,FIND(" ",F2,11)-2,2,""),FIND(" ",F2)+1,99)+0