Converting a string into a particular Date Format

[sahilmadan]

I have a large no of strings(left hand side of arrow) and i need to convert them into Date format(right hand side of arrow) in Excel.

19,690,408 --->> 04/08/1969
19,610,419 --->> 04/19/1961
19,560,107 --->> 01/07/1956

Can anyone help me with this?
Thanks in advance.

 

[JoeMo]

This will work for the data you posted, but what happens if the month number requires two digits (Oct-Dec)?

Excel Workbook
	A	B
1	19,690,408	04/08/1969
2	19,610,419	04/19/1961
3	19,560,107	01/07/1956
BU1

 

[sahilmadan]

Thanks for the reply.
But for A1 , the above formula is returning "25301" , not " 04/08/1969"

04/08/1969" .
​

 

[Gary's Student]

With data in A1, in B1 enter:

=DATE(LEFT(A1,2) & MID(A1,4,2),MID(A1,6,1) & MID(A1,8,1),RIGHT(A1,2))


​and format B1 as Date

 

[markmzz]

Another way:

=--REPLACE(REPLACE(SUBSTITUTE(A1,",",""),5,0,"/"),8,0,"/")

PS: Format the cell like date.

Markmzz

 

[sahilmadan]

It worked, but it is returning "4/8/1969" , and i need "04/08/1969" . Can it be done?

 

[Gary's Student]

Change the format to:

mm/dd/yyyy

 

[Rick Rothstein]

Here is another formula that should work...

=1*TEXT(A1,"0000-00-00")

Note: For this formula, and all the others your have received, you will need to use Format Cells (CTRL+1) to change the date serial number that to formulas are returning to the date display format that you want.

 

[sahilmadan]

Thank you so much everyone.

 

[sahilmadan]

The above formula is not working when i have "10" as the "mm" in the original format.
For ex,
19,791,021
19,851,025

When i apply that formula on values like above , it gives #Value Error