Date formatting

[Bingo58]

I have read all what is in the "Help" directory. I have a col of dates given in "CCYYMMDD" format and I want to insert a "/" between the year month and day but am only getting a whole row of "#####"'s. How do I get the date of 20021116 to read 2002/11/16 and format it all the way down the column. Sounds simple enough but I just cannot get it to work.

[Anne Troy]

This works to view your date, but won't change it to a date field:

0000"/"00"/"00

[Anne Troy]

This will give you the date in another column:

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

You can then take those dates, copy, edit and paste special as values. Then do the *Times One Fix*. Put a 1 in any cell. Copy it. Select the *bad dates*, hit Edit-Paste Special-Multiply (returns date serial number). Then format the cells as dates however you like.

[Mark W.]

On 2002-04-05 03:43, Bingo58 wrote:
I have read all what is in the "Help" directory. I have a col of dates given in "CCYYMMDD" format and I want to insert a "/" between the year month and day but am only getting a whole row of "#####"'s. How do I get the date of 20021116 to read 2002/11/16 and format it all the way down the column. Sounds simple enough but I just cannot get it to work.

1. After selecting your values choose the Data | Text to Columns... menu command
2. Press [ Next ] to 'til you reach Step 3 of 3.
3. Set the Column data format to Date: YMD and press [ Finish ].
4. Format as yyyy/mm/dd