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

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?
 

Question Info


Last updated March 24, 2020 Views 76,832 Applies to:
Answer
Answer
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.)
Regards,

Ron Coderre
Microsoft MVP - Excel

168 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Answer
Answer

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.

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

54 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.