Convert Text to Date format

[Jason Cambel]

Dear All,

Could you pls help me to convert the text to date format.

Input cell (A) = Tue 4/12/16

Expected outcome (B) = 12 April 2016 or 12 April

What formula i should use to get from A to B?

Thanks
Jason

 

[NARAYANK991]

Hi Jason ,

It all depends on your system date format ; if that is mm/dd/yyyy , then it is simple :

=MID(A1,FIND(" ",A1)+1,99)+0

or even

=MID(A1,5,99)+0

should convert the text in A1 to a valid Excel date. Thereafter , just format the cell which has this formula to dd mmmm yyyy or dd mmmm.

Narayan

 

[Khalid NGO]

Hello Jason,

you can try:
=TEXT(REPLACE(A1,1,4,""),"mm-dd-yyyy")

also change the date format as per your requirement.

Best Regards,
Khalid

 

[Somendra Misra]

Hi,

Another option:

=DATEVALUE(RIGHT(D16,LEN(D16)-FIND(" ",D16)))

Assuming D16 is holding your date.

Regards,

 

[azumi]

For "12 April" format :

=TEXT(DATE(1,1,TRIM(MID(SUBSTITUTE(A1,"/",REPT(" ",99)),100,99))),"dd")&" "&TEXT(DATE(1,LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),100,99)),SEARCH("/",TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),100,99)),1)-1),1),"mmmm")

you can add up year "2016" with @Somendra formula in post #3

=TEXT(DATE(1,1,TRIM(MID(SUBSTITUTE(A1,"/",REPT(" ",99)),100,99))),"dd")&" "&TEXT(DATE(1,LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),100,99)),SEARCH("/",TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),100,99)),1)-1),1),"mmmm")&" "&TEXT(DATEVALUE(RIGHT(A1,LEN(A1)-FIND(" ",A1))),"YYYY")

Regards