date format will not change in Excel

[Maxx]

I have a file with dates in one column, trying to change the date format but
the format command does not work. The values do not appear to be stored as
text, but will not change to any other format (general, number, currency,
etc) unless each cell is selected and edited (i.e. double click on the cell
then leave the cell) then the format changes. Too many cells to do this
manually. It only occurs in one column, all others change with the format
command. Tried pasting the values to another pre-formatted cell, no luck.
Anyone know why this happens?

 

[Jim Thomlinson]

I hate to break it to you but you have text instead of dates. There are a
couple of things to try... One is a find and replace. Find a slash or 0 or
some other character that exists in each of the cells and replace it with
itself. So replace 0 with 0 or / with /. When you do that you should get a
conversion from text to date.

You could also try placing a 1 in some cell somewhere. Copy it. Select the
dates and Paste Special... -> Multiply. That will force a conversion from
text to date.

 

[David Biddulph]

As you've apparently got text, try Data/ Text to Columns, and in the last
stage of the conversion wizard you'll need to tell it which text format you
are converting from, e.g. MDY

 

[Maxx]

Thx Jim, it worked. Saved a lot of time!
Maxx

I hate to break it to you but you have text instead of dates. There are a
couple of things to try... One is a find and replace. Find a slash or 0 or
some other character that exists in each of the cells and replace it with
itself. So replace 0 with 0 or / with /. When you do that you should get a
conversion from text to date.

You could also try placing a 1 in some cell somewhere. Copy it. Select the
dates and Paste Special... -> Multiply. That will force a conversion from
text to date.

 

[Jon Peltier]

Even quicker: Copy a blank cell, then use Paste Special > Add.

- Jon