New question on formatting dates from 20090218

[GoBonnieGo]

I have a column of dates typed as text like +20090218. I've searched this
board for ways to get them into a more readable format such as 2/18/2009.
I've followed everyone's suggestion (and done it on the column as text and as
numbers) and the best I can get is that it comes out as # signs. Usually to
me that means expand the column. When I do that, it's just number signs as
far as I can see.

So far, that's the only way it changes. Usually, it doesn't change at all.

More suggestions would be appreciated?

Bonnie

 

[Sheeloo]

All of them have a plus sign in front of them? All of them have 8 digits?

If yes and assuming they are in Col A

then put this B1
=MID(A1,2,4)

this in C1
=MID(A1,6,2)

this in D1
=RIGHT(A1,2)

and finally this in E1
==DATE(B1,C1,D1)

Copy the formulas down...

If it does not work then pl. upload to wikisend.com and paste the link here
or mail it to me... I will fix and send back.

You can Copy and PASTE SPECIAL as VALUES once you are happy with the
results...

 

[Shane Devenshire]

Hi,

Highlight all the dates, and choose Data, Text to Columns, choose Fixed
Width, Next. In the data preview area click between the + and the date.
Click Next. In the data preview area select the first column, the one with
the + and click Do not import (Skip). Select the second column, your dates
and open the Date drop-down and choose YMD. Click Finish.