Change to date format

[Lakshmi Narain]

Dear all,
PFA, Please help me to change to date format.

 

[Somendra Misra]

Hi Lakshmi,

Try below formula in B2 and copy down:

=DATEVALUE(MID(SUBSTITUTE(TRIM(A2)," ","/"),FIND("/",SUBSTITUTE(TRIM(A2)," ","/"))+1,FIND("|",SUBSTITUTE(SUBSTITUTE(TRIM(A2)," ","/"),"/","|",2))-FIND("/",SUBSTITUTE(TRIM(A2)," ","/"))-1)&"-"&LEFT(SUBSTITUTE(TRIM(A2)," ","/"),3)&"-"&RIGHT(SUBSTITUTE(TRIM(A2)," ","/"),4))

Than custom format the date as per your choice.

Regards,

 

[Khalid NGO]

Hi,
Just adding another approach:
=DATEVALUE(SUBSTITUTE(MID(A2,5,2)," ","")&"/"&LEFT(A2,3)&"/"&TRIM(SUBSTITUTE(RIGHT(A2,5)," ","")))

Regards,

 

[Khalid NGO]

@Lakshmi,
You can remove the TRIM (in my formula) to shorten more.