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

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

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.