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?

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.


--
HTH...

Jim Thomlinson


"Maxx" wrote:

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?

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
--
David Biddulph

"Maxx" wrote in message
...
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?

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

"Jim Thomlinson" wrote:

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.


--
HTH...

Jim Thomlinson


"Maxx" wrote:

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?

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Jim Thomlinson" wrote in message
...
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.


--
HTH...

Jim Thomlinson


"Maxx" wrote:

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?