How to convert a text format to date format in excel

 Suppose we are given the following TEXT

8/26/1961. Let that it is in the cell J2

We would like to convert it to Date

8- month

26- day

1961 - year

So the formula to convert to date is

Date(year, month, day ) in yyyy,mm,dd format

So we go as follows

Year: RIGHT(J2,4)

Month: LEFT(J2,FIND("/",J2)-1)

Day: MID(J2,FIND("/",J2)+1,2)

So the formula becomes

DATE(RIGHT(J2,4),LEFT(J2,FIND("/",J2)-1),MID(J2,FIND("/",J2)+1,2))

How to deal with some wrong and some true dates

=IF(ISNONTEXT(C3),C3,DATE(RIGHT(C3,4),LEFT(C3,FIND("/",C3)-1),MID(C3,FIND("/",C3)+1,2)))