Date format not refreshing

[Guest]

I want all the birthdates to be in the form dd/mm/yyyy but the data was
imported into the spreadsheet in the form yyyy-mm-dd. I've gone into the
cell format menu and changed the format to the one I require but the cells
refuse to update to the new format. The only way I have managed to get them
to change is by putting the cursor in the cell and hitting return. When I do
this the cell refreshes to the correct formatting. The thing is, I have a
data base with hundreds of dates in it, and I'm getting bored with having to
click into each cell to make it change to the new formatting.

 

[Gord Dibben]

Try selecting an empty cell and copying it.

Select all other cells and Edit>Paste Special(in place)>Add>OK>Esc.

Also make sure calculation mode is "Automatic" at Tools>Options>Calculation.


Gord Dibben MS Excel MVP

 

[Guest]

Thanks, your first suggestion worked, although I can't understand why???

 

[Gord Dibben]

Excel stores dates as serial numbers.

Your "numbers" being imported were seen as "text".

The copy/paste special changes them back to the serial numbers.


Gord