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...

 

[dtdionne]

I feel you're pain SusaninHouston. Here it is 2 years later and this GD MF just cost me $5M in my budget this year because I spent the last 9 GDMFing days trying to rid myself of this disgusting embarrassment.

Makes me want to round up all Microsofts kids and turn them into farts, puke, warts, and/or herpes then look at all the parents and say...."golly gee wizo there folks, how'd that happen, eww yuck they stink, your children stink and oozz of puss, YUCK....but hey!!! look on the bright side happy faces, there are so sooo many forums out there with answers. Yes, yes, I know, CALM DOWN! the smell, its the smell right? WOW. Anyway, as I was saying before getting interrupted by the stinky mess that was once your little ones, YYEESSS, you can stop whinning, I'm VERY WELL aware that I DID THIS TO YOUR CHILDREN, but does that really matter? those forum people are such hard workers...they're willing to slave individually and together on all the sssshiiiiitttttt you create, certainly they'll do the same for me...no? oh you silly silly little people, THE SMELL!!!, SURE they will...you all are so cute!!!, i could just pinch your cheeks. Now go take care of this stinky disgusting mess I just made out of your children!! What kinda parents are you anyway! Sheesh"

 

[dtdionne]

I feel you're pain SusaninHouston. Here it is 2 years later and this GD MF just cost me $5M in my budget this year because I spent the last 9 GDMFing days trying to rid myself of this disgusting embarrassment.

Makes me want to round up all Microsofts kids and turn them into farts, puke, warts, and/or herpes then look at all the parents and say...."golly gee wizo there folks, how'd that happen, eww yuck they stink, your children stink and oozz of puss, YUCK....but hey!!! look on the bright side happy faces, there are so sooo many forums out there with answers. Yes, yes, I know, CALM DOWN! the smell, its the smell right? WOW. Anyway, as I was saying before getting interrupted by the stinky mess that was once your little ones, YYEESSS, you can stop whinning, I'm VERY WELL aware that I DID THIS TO YOUR CHILDREN, but does that really matter? those forum people are such hard workers...they're willing to slave individually and together on all the sssshiiiiitttttt you create, certainly they'll do the same for me...no? oh you silly silly little people, THE SMELL!!!, SURE they will...you all are so cute!!!, i could just pinch your cheeks. Now go take care of this stinky disgusting mess I just made out of your children!! What kinda parents are you anyway! Sheesh"

"Oh yeah, and GOOD LUCK!!! I know you can do it!!" Now lets see, yeup, im gonna go work new, inventive, fresh, forward thinking ways of sticking right, and I mean like right in your a$$...but BIGGER, FASTER and with with never before seen, unprecedented ferocity! ohhhhhhhhhhhh, I CANT WAIT!!!!!!!!!!!!! I hope you can't either...WINK, WINK, SMOOTCH SMOOTCH. OHHH YYYEEEAAAAAHHHHHH!

 

[SusaninHouston]

"Oh yeah, and GOOD LUCK!!! I know you can do it!!" Now lets see, yeup, im gonna go work new, inventive, fresh, forward thinking ways of sticking right, and I mean like right in your a$$...but BIGGER, FASTER and with with never before seen, unprecedented ferocity! ohhhhhhhhhhhh, I CANT WAIT!!!!!!!!!!!!! I hope you can't either...WINK, WINK, SMOOTCH SMOOTCH. OHHH YYYEEEAAAAAHHHHHH!

I feel your pain --

But as it turns out, response # 19 is actually right on point... Click on the link and look for the answer posted by Ron Coderre 2/10/2010 -- first one. (I cut and pasted below) He talks about CELL STYLES, which is a more GLOBAL form of formatting, and a feature I had not, at the time this post was made, gotten into, but since have. From the run-ins I've had with this problem since then, it does definitely appear to be the causal factor.

Rather than explain the whole thing here, look up Cell Styles and how to use them. It does fix / prevent the problem - though cell styles have their own set of rules and hiccups.

Apply, create, or remove a cell style - Excel - Office.com

'============================================================================================================================================
This bug has been posted in many forums. Apparently, the number format in the NORMAL style spontaneously changes from General to this date format ([$-409]m/d/yy h:mm AM/PM;@). This typically happens in shared workbooks, but I have seen it happen in one of my workbooks...which was not shared.

Since a change to the NORMAL style impacts every cell that has not been specifically formatted, the end result is seemingly devastating. The fix for any particular workbook, however, is relatively easy.

To resolve that issue:
• Home.Cell_Styles
...Right-click: NORMAL...Select: Modify
...Click the Format button
...Number_Tab....Category: General

To my knowledge Microsoft has not addressed this XL2007 issue via an update or patch.