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.

 

[Rick Rothstein]

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

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

When you have several responses like in this thread, you cannot just say "that formula doesn't work"... we do not know which formula you are talking about. If you used "Replying With Quote" instead of "Reply", then the message with the formula would be shown and we would know which formula you were talking about.

Now, with that said, I am pretty sure my formula (shown in Message #8) will work with those numbers as well as your original ones.

 

[Gary's Student]

My formula gives:

10/21/1979

10/25/1985

<colgroup><col></colgroup><tbody>
</tbody>

 

[sahilmadan]

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

The above formula is not generating the correct values.
For ex . In case of "19,690,408" it is generating output as "12/8/1993".
Could you please look at the formula again?

C​

 

[Gary's Student]

My formula assumed that the commas were actually part of the data.
You are seeing errors because in your sheet the commas are only formatting.

 

[sahilmadan]

When you have several responses like in this thread, you cannot just say "that formula doesn't work"... we do not know which formula you are talking about. If you used "Replying With Quote" instead of "Reply", then the message with the formula would be shown and we would know which formula you were talking about.

Now, with that said, I am pretty sure my formula (shown in Message #8) will work with those numbers as well as your original ones.

Sorry about that. Thank you for your help.

 

[JoeMo]

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

04/08/1969" .
​

That's correct. Just format the column as "Custom" mm/dd/yyyy.

 

[Gary's Student]

Here is the alternate formula:

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

 

[mark91345]

Thank you. I had the same issue and your formula helped me.

What does the "0" do (beginning after the first parenthesis)? I can't figure it out.