Rearranging a date

[Guest]

Is there any simple way to convert text in Excel

19721221

to: 12-21-1972 ?

We are often sent birthdates in the top format and HAVE to have it
mm/dd/yyyy. I have been using the =LEFT function to split the text out,
rearrange, then =CONCATENATE to put it all together again. I'm having
problems though when dates are mixed with a month containing only one value.
Example: 1972314 / 3-14-1972

If anyone can figure this out ---they are a genius!

 

[Gord Dibben]

Janice

Data>Text to Columns>Next>Next>Column Data Format>Date>YMD and Finish.


Gord Dibben MS Excel MVP

 

[Guest]

There can't be a unique answer if both the month and the day can be one or
two digits.

If you have 197121 Is this January 21 or December 1?

Yureally need to get the data generated in a fashion easier to use.

I had to write a very similar Custom function using dots between the fileds
like

12.3.06
I could write the function because I got unique answers.

 

[Gord Dibben]

Ooops!

Missed the 1972314 part.

T to C won't see that as a date.

Gord