Unwanted Change to Date Formatting

[SusaninHouston]

I'm an experienced Excel user running into an annoying little problem in some of the models I've built. I can't seem to find any connection between when it happens and when it doesn't...

The problem is, a number that has been formatted in a Numeric format in a locked cell in a password-protected worksheet suddenly changes to a Date format.

I've never seen it happen on my own computer in a model that I personally am using, but I have seen that it has happened numerous times in models that I wrote and support.

As a developer, it s my habit to format every cell that I use (rarely General or Text), and almost never to format cells I don't use (entire columns, rows or sheets). So these cells - the ones that changed - were definitely formatted.

But they appear to change themselves -- Any clues?

My on-line searches just get me a primer on how to format numbers...

 

[venkat1926]

it happens to me also . perhaps some glitch in excel.try to format cells as general
in that case string will be automatically formatted as text and numbers as numbers.
see whether it help. if you type by mistake 1/1 instead of =1/1 then also this problem may occur.

 

[SusaninHouston]

Thanks VenKat -- In some cases, the cells that changes were formatted as general and a simple 321 would change to it's date equivalent (11/16/1900). In most cases, they were formatted as either currency or comma-formatted numbers. In all of the cases I've seen, it was strictly a *format* change, never a value change -- so 321 didn't go to 3/21. To me, that implies that / or - characters were not part of the problem.

 

[SusaninHouston]

Let's try this again...


In a locked and password-protected worksheet, locked cells that have been formatted one way (say numeric, comma with no decimals -- or sometimes left unformated as general) will suddenly switch to a date format. When it happens, it's usually a discrete range that format-shifts -- a row of 'year' labels or a table of numbers -- so not at the single-cell level. When it happens in one spot in a workbook, it it frequently discovered in other spots as well. But it is NOT global to all workbooks from the same template, so it might happen to 1 of 50 otherwise identical workbooks. The underlying numbers are NOT changed -- just the format -- but since the change is to a date format, the numbers often *look* very wrong.

It has never happened to me directly on my computer, but I have seen it happen repeatedly to other people using models I have built and support.

Anyone know why this happens -- or better yet, how to prevent it from happening?

 

[SusaninHouston]

and yes, I HAVE searched for an answer but never found either an explanation or a remedy.

 

[SusaninHouston]

One other point of clarification -- These are not INPUT cells that change, but locked rather labels or simple calculations, so 'user-entered the wrong thing' -based explanations aren't it...

 

[SusaninHouston]

Anyone?

After my original post from three months ago failed to produce any useful suggestions, I tried searching for answers again, then asked the question again this morning.

But instead of attempting to answer it, I received a reprimand for the 'duplicate post' and the thread was killed -- still unanswered.

Hence, this old post -- still with no answers.

 

[SusaninHouston]

Bumping up in search of one good answer...

 

[SusaninHouston]

Bumping for help again...

 

[SusaninHouston]

Maybe if I talk to myself long enough, someone will think this is interesting enough to take a stab at...