Ok, this one really did me in. I have lost hundreds of workhours over this already. Its been posted elsewhere:
MSFT: Excel 2007 spontaneously formats entire work book in date format -- - Microsoft Community
MrExcel: http://www.mrexcel.com/forum/excel-questions/603555-unwanted-change-date-formatting.html
Other Forum: Spreadsheet's numbers change to date
No proper fix AFAIK. I want to make one thread where we all can check at least what's causing it.
So the issue is:
I have an Excel 2007 workbook with no problems for years. Suddenly, one day I open it and all the formatting in the sheet is messed up - the problem is with formatting only. All the numbers are being displayed as dates. Others have reported the same issue with everything becoming currency. The change in formatting reoccurs again randomly.
It seems there is some corruption sometimes of the Normal template. Two temporary fixes:
1. Unprotect all sheets, then Format Cells > Custom > delete all the custom formats like [$-409]m/d/yy h:mm AM/PM;@ etc.
2. From the MVP in above link:
Home> Cell Styles
...Right-click: NORMAL...Select: Modify
...Click the Format button
...Number_Tab....Category: General
Both solutions are temporary. Plus they are not full solutions - what happens is all the numbers are restored to their old formatting BUT all dates lose their format, and are also converted to numbers like 40164 which you need to manually fix the formats of! After a while, again on opening the sheet, it randomly goes back to messed up displays. I have a big setup of 30+ machines and by now 3 of them have this problem and we are utterly dependent on Excel. I have lost a lot of productivity and am in fear of a new person bringing this to me. It happened suddenly - all 4 machines reported it within a week - so I don't know if its spreading - we do open files of one computer on another. Further, I can confirm that even new files created and worked on from a machine where this has happened before are randomly then infected with the same problem! Which makes it so serious.
Four possible causes/symptoms from all the threads I have read so far:
1. Shared Workbooks:
to me happens on some workbooks which are shared, and others which are not.
2. Protected Sheets:
to me same thing, happens in xlsx with protected and no protected sheets.
I'm putting my money on these:
3. Control Panel > Regional Settings changed:
I have played around with these, but still never had this problem before.
4. Multiple instances of Excel with copy-paste across them:
This I do all the time.
Anyone tried something? Will formatting the machine fix this? What about files which are already messed up?
Dang you Microsoft. If you have seen this bug or have any ideas, pls post..
MSFT: Excel 2007 spontaneously formats entire work book in date format -- - Microsoft Community
MrExcel: http://www.mrexcel.com/forum/excel-questions/603555-unwanted-change-date-formatting.html
Other Forum: Spreadsheet's numbers change to date
No proper fix AFAIK. I want to make one thread where we all can check at least what's causing it.
So the issue is:
I have an Excel 2007 workbook with no problems for years. Suddenly, one day I open it and all the formatting in the sheet is messed up - the problem is with formatting only. All the numbers are being displayed as dates. Others have reported the same issue with everything becoming currency. The change in formatting reoccurs again randomly.
It seems there is some corruption sometimes of the Normal template. Two temporary fixes:
1. Unprotect all sheets, then Format Cells > Custom > delete all the custom formats like [$-409]m/d/yy h:mm AM/PM;@ etc.
2. From the MVP in above link:
Home> Cell Styles
...Right-click: NORMAL...Select: Modify
...Click the Format button
...Number_Tab....Category: General
Both solutions are temporary. Plus they are not full solutions - what happens is all the numbers are restored to their old formatting BUT all dates lose their format, and are also converted to numbers like 40164 which you need to manually fix the formats of! After a while, again on opening the sheet, it randomly goes back to messed up displays. I have a big setup of 30+ machines and by now 3 of them have this problem and we are utterly dependent on Excel. I have lost a lot of productivity and am in fear of a new person bringing this to me. It happened suddenly - all 4 machines reported it within a week - so I don't know if its spreading - we do open files of one computer on another. Further, I can confirm that even new files created and worked on from a machine where this has happened before are randomly then infected with the same problem! Which makes it so serious.
Four possible causes/symptoms from all the threads I have read so far:
1. Shared Workbooks:
to me happens on some workbooks which are shared, and others which are not.
2. Protected Sheets:
to me same thing, happens in xlsx with protected and no protected sheets.
I'm putting my money on these:
3. Control Panel > Regional Settings changed:
I have played around with these, but still never had this problem before.
4. Multiple instances of Excel with copy-paste across them:
This I do all the time.
Anyone tried something? Will formatting the machine fix this? What about files which are already messed up?
Dang you Microsoft. If you have seen this bug or have any ideas, pls post..
