Format cells

[austenr]

How do you format a cell so if you enter 08162010, you end up with 08/16/2010?

[Sir Babydum GBE]

How do you format a cell so if you enter 08162010, you end up with 08/16/2010?
Don't think you can - not using format anyway. It has to recognise the input as a date before it can format it and I don't think that is one of the acceptable ways of entering the date in excel

perhaps a macro to turn it into a date?

[austenr]

thanks i was hoping

[Genteel]

I'm also having a problem with formatting cells. While it may be a bit different, the essence of the problem is the same.

I have cells that are entered in as Text Format, or in the General Format. If I highlight those cells, and reformat at a number, nothing changes with the text. The data was originally brought in using SQL or MSQuery which standardizes the format across all the data.

How can I reformat those cells in a column (quickly) so I don't have to go to each cell, press F2, then press enter - ugh! This is really annoying! When I have a list of over 5,000 items, this is in effect impossible. I'm thinking this is really difficult as even the formatting from within Excel is not able to change the data, I have to manually perform the editing of the cell.

Either a macro or some other way would be helpful. I am using Excel 2003 on WinXP Pro at work for this.

[Sir Babydum GBE]

I'm also having a problem with formatting cells. While it may be a bit different, the essence of the problem is the same.

I have cells that are entered in as Text Format, or in the General Format. If I highlight those cells, and reformat at a number, nothing changes with the text. The data was originally brought in using SQL or MSQuery which standardizes the format across all the data.

How can I reformat those cells in a column (quickly) so I don't have to go to each cell, press F2, then press enter - ugh! This is really annoying! When I have a list of over 5,000 items, this is in effect impossible. I'm thinking this is really difficult as even the formatting from within Excel is not able to change the data, I have to manually perform the editing of the cell.

Either a macro or some other way would be helpful. I am using Excel 2003 on WinXP Pro at work for this.

I think this might work: in G2 (or another available column) use "=F2+0" then copy that formula down as many cells as necessary, then past that column of formulas onto column F using PasteSpecial - Values.

Any good?

[Bob Phillips]

@Genteel, Or just do Data>Text To Columns on the column and on the third tab in the dialog, select the date format.

[CPINNA1]

You can break down the cell using a series of =Left, Mid, Right formulas and concatenate the result with a?. Just keep in mind that the zero in front typically drops off thus you will need an If statement that shows if the left formula returns a number greater than 12 then you want the Left of only 1 space over instead of 2.

[CPINNA1]

=RIGHT(A1,4)*1 WILL GIVE YOU THE YEAR


=IF(OR(LEFT(A1,2)*1>=10,LEFT(A1,2)*1<13),LEFT(A1,2)*1,LEFT(A1,1)*1)

WILL GIVE YOU THE MONTH


=IF(AND(LEN(C1)=2,MID(A1,3,2)*1>31),MID(A1,3,1)*1,MID(A1,3,2)*1)

Will give you the day assuming the Month is present in the cell C1

Just concatenate the results

[Genteel]

I think this might work: in G2 (or another available column) use "=F2+0" then copy that formula down as many cells as necessary, then past that column of formulas onto column F using PasteSpecial - Values.

Any good?

***pop!****
***sssssssssss***

:thumb

Sir Babydum, that's the sound of champagne. This is much easier and less time consuming than copying to Word, and pasting back in a new column.

I probably already knew that, but I am sure this site is here for just such issues! Thanks a million. :beerchug: