Rearranging a date
 

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!

Rearranging a date
 

Janice

DataText to ColumnsNextNextColumn Data FormatDateYMD and Finish.


Gord Dibben MS Excel MVP

On Wed, 14 Mar 2007 09:58:16 -0700, Janice B. - Cleveland, Ohio
wrote:

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!

Rearranging a date
 

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.

"Janice B. - Cleveland, Ohio" wrote:

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!

Rearranging a date
 

Ooops!

Missed the 1972314 part.

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

Gord

On Wed, 14 Mar 2007 11:10:00 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

Janice

DataText to ColumnsNextNextColumn Data FormatDateYMD and Finish.


Gord Dibben MS Excel MVP

On Wed, 14 Mar 2007 09:58:16 -0700, Janice B. - Cleveland, Ohio
wrote:

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!