Excel 2007 spontaneously formats entire work book in date format -- seems to be a bug -- is there a solution?

Anonymous

Hello, I am having a problem with Excel 2007 which I infer other people also have. In a large workbook, that has been in use for some time, suddenly one finds that virtually the entire workbook has formatted every cell as a date. Here are some web references indicating that other people are having the same problem.

http://www.eggheadcafe.com/software/aspnet/33427643/default-cell-format-chang.aspx

http://www.pcreview.co.uk/forums/thread-3620548.php

If this is not a bug, it is an unfortunate aspect of the user interface that so many people are running into it with no understanding of what they have done that caused it to happen. Is there some solution?

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous

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.

Best regards,

Ron Coderre

Microsoft MVP - Excel (2006 - 2010)

P.S. If any post answers your question, please mark it as the Answer (That way it won't keep showing as an open item.)

0 comments

Answer accepted by question author

Anonymous

Hi,

Possible causes of the problem are:

1. Conditional formatting rule may be applied to the cells.

2. A custom cell style may be applied to a cell / cells.

3. Default cell style Normal may be modified for number from general to date.

4. Possible file corruption.

Solution to the specified problems:

Note: Please try these troubleshooting steps on a duplicate file created by using “Save as” option on the original file.

Method 1: Finding the Formatting applied across the sheet and Clearing Conditional Formatting.

1. Click on ‘Conditional formatting’ under Home tab and then on ‘Manage Rules…’

2. Now select’ This Worksheet’ for ‘Show formatting rules for:’

3. Check if any rules are created for ‘This worksheet ‘, if yes then open the rule and see whether the rule is set to change the cell format to other format and if needed change/delete the rule according to your requirement.

Method 2: Modifying the default /custom style in Excel.

1. Click ‘Cell Styles’ under Home tab and check if you have a Custom style created,

If yes then right click on the Custom style created, select modify and click format, now select General under Number tab, click OK and OK again on the Style window.

If No then right click on Normal and check if Number has General format if not then select modify and click format, now select General under Number tab, click OK and OK again on the Style window.

Method 3: Try to repair the file.

Note: Do not try to repair the original file, repair a copy of the file instead.

Open Excel, click the office button > Open > In the Open dialog box, select the file you want to open, and click the arrow next to the Open button. Click Open and Repair, and then choose Repair to repair the file.

Check for more information on file repair techniques and recovering data from the corrupted file by clicking on the link below.

http://office.microsoft.com/en-us/excel/HA100970171033.aspx?pid=CH100948241033

As an alternate, select the cells/range where the data is displayed incorrectly and click on clear format to clear any unwanted formatting in the cell.

Niranjan I K Microsoft Answers Support Engineer.

• We appreciate your participation in MS Forums, Help us understand your needs better. To share your valuable Feedback please Click here.

0 comments

102 additional answers

Anonymous

2013-12-12T14:26:59+00:00

I agree with your comment but I guess a small list of users don't have a big enough stick to make a change. I found the problem to be in at least 3 versions of excel and understand the different ways of correcting the issue but nobody has seemed to pin point a option that seems to cause the problem. I found that using the format drop down that the Date format to format new cells would wreck the work book and add the code in the custom format list [$-409}dddd,mmm dd,yyyy , I have gone to using the custom date format and that solved the problem , but still don't under stand why just a couple of the work books have this problem.
Was this answer helpful?

0 comments No comments
Anonymous

2013-12-12T03:28:31+00:00

That works, First of all, before I say anything else: "THANK YOU!!!" BUT, here's the real problem, is that 1) It is a bug. 2) Microsoft should be responsible for that bug, 3) Microsoft should 'FIX IT' on their end, without ANYONE having to go through that, especially, if is the case, they have to correct for every worksheet (which, I don't remember at this point how that aspect of it worked it).

What I do know, is that those for those who teach VBA and different Microsoft classes, etc. this should be a very common "at the top of the list" Helpful Tip, because it is a VERY frequent occurrence in my experience, happening to me not only at home in Excel 2010, but also at job sites where they had 2007, and where they had 2010, and I did nothing unusual. What I did notice though, was that it seemed to often occur after I changed the file type from regular .xlsx to .xlsm.

Again, this is NOT acceptable, as I have worked quite extensively with Excel since about 2000, I did not have any such problem until the release of 2007, and IF (IF) Microsoft is considering themselves as a leader, they should not leave such a bug 'unfixed', and I can only hope this is not occurring in Versions later than 2010. I am quite sure that if something anything near like this was happening in a software frequently, or 'by default' used in McIntosh, that (if he were still alive) Steve Jobs would have a fit about it, and not allow such a thing. Now some might say: "Oh, that's a small thing", but really it is not a "small thing" when I'm working for a company, when I've put some time into a file, and when (due to my loyalty to Microsoft, or the loyalty of the company I am working for to Microsoft?) I am suddenly made to "look bad", due to irresponsibility of Microsoft to "FIX" what has become a fairly well known, and constant bug. No, that is NOT ACCEPTABLE, but thank you for providing a feasible answer, I do appreciate that part. Microsoft would do VERY WELL to first focus on fixing what was wrong on one version before adding new things to their new version. Nice that they add new features, but not so nice that they don't address poor (whatever the case) poor interface, or bugs on version prior to newest. And their 'Printing' of files, and page adjustment, and having to go to separate screens in some instances when one should not have to, that's not too good either, though going beyond the scope of this focus here (but not beyond the scope of Microsoft's tendencies). Please excuse me for going on, but I think it needful that I do, because I have not heard of Microsoft offering the "fix" for either 2007 or 2010 yet regarding that. Question: WHEN will Microsoft address it, and do so? Isn't that the real base-line Question? Microsoft Customer Support and Product Responsibility?
Was this answer helpful?

0 comments No comments
Anonymous

2013-12-12T01:27:10+00:00

I spent an entire work week developing an excel file. All looked fine when I saved for the final time. When a colleague opened to review the formatting was randomly changed and deleted. ie font style and size changed random borders deleted and added. I try to fix it and the next time I open the file different formatting has been changed.

It looks like many other people have the same issue and the fix above works for some people.

Unfortuantely, I don't understand the fix... What is it? Do I type that somewhere? It looks like some kind of code that has to be manually typed somewhere but I have never done anything like that in excel.

Could someone please explain how to input the above information?

Thank you so much!!

Hopefully you've already figured this out, but just in case...

You don't type, it's instructions on where to click.

On the Ribbon at the top:

Click Home, then in the Styles section click Cell Styles

Right click on Normal in the popup that appears, then click Modify

Click the Format button

Click the Number tab if not already on it

Select General in the Category box

Click OK

Click OK
Was this answer helpful?

0 comments No comments