Number formsts converted to date format

[nupnin]

I have a workbook that randomly has cells formatted as numbers change to
cells formatted as dates. I have found two other web forums that discuss
this topic:

http://help.wugnet.com/office/Excel-2007-Cell-Format-Change-Bug-Warning-ftopict1143233.html
http://archive.midrange.com/pctech/200907/msg00095.html


I don't see it here so I will start it. There is no obvious action that
results in this behavior. The workbook contains several formats. The only
ones than change are cells formatted as General and cells formatted as
Number. This happens infrequently and randomly.

The workbook does have some VBA in it. there is no code that changes cell
formats. There are some cell formulas and some VBA statements that convert
text to numbers and numbers to text.

Any ideas??

 

[Gary''s Student]

It is possible for a cell to re-format itself. For example:

1. format A1 as Text and enter 1/1/2009
2. copy A1 and paste/special/values onto A2
3. A2 will APPEAR to be text, but
4. click on A2 and touch F2 and then ENTER and
5. A2 re-formats itself to Date!

Editting a cell gives Excel a "second chance" to guess the format to use.

 

[nupnin]

Thanks very much for the response. I was not familiar with the behavior you
described. Unfortunately that is not what is happening. Number formats on
protected sheets are being changed to date formats.

 

[Gary''s Student]

Show us an example of the value as entered and how it was changed.

 

[nupnin]

One sheet has many cells with formulas in it that yield simple integer
numbers. e.g. 3 which looks fine until some random time in the future it
changes to 1/3/1900. All of the other numbers on that sheet are also changed
to dates. Other sheets have numbers only - no formulas - like 2009 which
becomes 7/1/1905

 

[WSL]

I have been experiencing the exact same problem. It also appears to be
random, the cells that convert to a date were not being edited or changed in
any way. I went to the trouble of highlighting every cell in the worksheet
and changing the format back to general, then going back to all cells that I
wanted to have specific formats, like dates, or currency or whatever. A week
later I am seeing the issue again. Random cells throughout the worksheet are
in date format again, and blank cells are also date formatted.

Thanks for the links to the other threads...any news yet?

 

[nupnin]

No news.

I have been experiencing the exact same problem. It also appears to be
random, the cells that convert to a date were not being edited or changed in
any way. I went to the trouble of highlighting every cell in the worksheet
and changing the format back to general, then going back to all cells that I
wanted to have specific formats, like dates, or currency or whatever. A week
later I am seeing the issue again. Random cells throughout the worksheet are
in date format again, and blank cells are also date formatted.

Thanks for the links to the other threads...any news yet?