ReFormatting dates in a cell.

[MikeyZ]

my cells (500) have their dates like this: 03/15/2011.
I hit F2, and reformat it to read 3/15.
Then use the paint brush to change the others, but it doesn't change unless I hit F2 for each one!!!!

Any idea what I'm doing wrong??

Mike

 

[bryonwoods30]

after you change the first one, you can copy it(control c), then highlight all your others and paste special then formats.

 

[MikeyZ]

All the other dates are different?
Any suggestions other than F2 and Enter for each and every one!!

Mike

 

[MikeyZ]

Copying Formats and Paste special doesn't work.

 

[bryonwoods30]

if you copy the first date that you formatted to what you want, highlight the others then paste special and choose format. this will only change the formatting of the highlighted cells to match the one you copied, not the values in them.

 

[bryonwoods30]

just highlight all of them and go to format cells and choose the format you want

 

[MikeyZ]

Just to clarify.
I have all the dates formatted as follows:
01/10/2011

I want them as:
01/10

I went in and changed the format from one to the other.
Copied the format to all the other cells.
They remained the same.... 01/10/2011.

I then tried using the Painbrush.
They remained the same: 01/10/2011.

If I select one of the cells with 01/10/2011 (format),
Hit f2, then enter, it changes nicely to 01/10.

Which means I would have to do the same for each and every cell:
F2 then enter.

I was hoping the Paintbrush or copy Format Special would work.
They don't.

Mike

 

[Haseeb Avarakkan]

Looks like your dates stored as text. Select the range press CTRL+F, Find What: /, Replace with: /

Then change the format.

 

[MikeyZ]

It is all stored as General.
When I change it to "DATE" and select the Format I want.
It still remains as General until I hit F2 + Enter.
Then it changes to the Format I want.
Not that it matters, but it's Excel 2007.

 

[MikeyZ]

Oh. One last tidbit of info.
The data came from an ACCESS file.
However, when I copy and paste all the data to a spreadsheet I paste it as Values. So from there I should be able to change formats etc.
Seems like the DATE always gives me trouble.