Text to date: 20010102 to ?date format

texasalynn

Well-known Member

Joined

May 19, 2002

Messages

8,458

• Dec 19, 2003

• #2

What about this?

=MID(A1,5,2)&"/"&RIGHT(A1,2)&"/"&LEFT(A1,4)

HTH
texasalynn

 

Upvote 0

Y

Yogi Anand

MrExcel MVP

Joined

Mar 12, 2002

Messages

11,454

• Dec 19, 2003

• #3

Hi Abbasi:

Select the cells to convert, then invoke DATA|Text_to_columns, then Next,Next, then in cColumn_Dat_Format, choose the Date Option, then select YMD, then Finish

that should do it!

 

Upvote 0

A

aabbasi

Board Regular

Joined

Mar 4, 2002

Messages

188

• Dec 19, 2003

• #4

Hi Yogi and texasalym:

Thank you both of you for a quick response. Texasalym: your solution worked for me. Unfortunately, Yogi i was not able to solve with your suggestion. may be I did something wrong !

 

Upvote 0

texasalynn

Well-known Member

Joined

May 19, 2002

Messages

8,458

• Dec 19, 2003

• #5

Hey Yogi - that was cool. I did not know that would work like that. Much better way to convert.

Thanks . . . texasalynn

 

Upvote 0

Y

Yogi Anand

MrExcel MVP

Joined

Mar 12, 2002

Messages

11,454

• Dec 19, 2003

• #6

aabbasi said:

Hi Yogi and texasalym:

Thank you both of you for a quick response. Texasalym: your solution worked for me. Unfortunately, Yogi i was not able to solve with your suggestion. may be I did something wrong !

Click to expand...

Hi Aabasi:

The beauty of doing it with DATA|Text_to_columns is that no formula is needed, and the conversion can be made either in place or ported to another cell.

Now why it did not work for you -- I can not say. You have not stated whether you could get to the right tab in DATA_text_to_columns, and whether you made the right selections.

I would suggest try it again, and post back whether it works for you -- otherwise explain a little further and then let us take it from there.

 

Upvote 0

Y

Yogi Anand

MrExcel MVP

Joined

Mar 12, 2002

Messages

11,454

• Dec 19, 2003

• #7

texasalynn said:

Hey Yogi - that was cool. I did not know that would work like that. Much better way to convert.

Thanks . . . texasalynn

Click to expand...

Hi texasalynn:

Yes -- it is the prefered way -- because no formula is needed -- and of course as I mentioned to Aabasi, the conversion can be made either in place or the conversion can be posted in another cell -- especially if it is necessary to retain the original data for tracking or for audit trail.

 

Upvote 0

A

aabbasi

Board Regular

Joined

Mar 4, 2002

Messages

188

• Dec 19, 2003

• #8

Hi Yogi:

Sorry for being not clear.

This is hat I did:
I selected the cell. Then went to Data- Text-to-Colum, slected Delimited
then clicked Next, again Next
under Column Format, selected Date MDY
and clicked Finish

Nothing Changed !

 

Upvote 0

M

Mudface

MrExcel MVP

Joined

Feb 18, 2002

Messages

3,339

• Dec 19, 2003

• #9

I'd imagine you'd need YMD, the same format as your text date.

 

Upvote 0

Von Pookie

MrExcel MVP

Joined

Feb 17, 2002

Messages

13,686

• Dec 19, 2003

• #10

I have to agree with Mudface. I tried it using MDY and nothing happened, but with YMD it works perfectly and translated todays date from 20031219 to 12/19/2003

 

Upvote 0