Default Number Format in Excel 2010 worksheet randomly changed to a Date Format

[doreanna]

I have been working in a worksheet with 10 tabs, and suddenly all of my numbers turned to long date format. I manually changed them all back to what they were. General, Currency, %, etc. but the default seems to be stuck in the long date format. When I create a new tab it is automatically all long dates. Very strange.
Oh, and the other weird thing is when I send it to other people, some people see the correct formats and when other's open it the numbers all go back to the long date format. VERY Frustrating.
Would love to get some advice.

 

[LeeK87]

seems to be a sporadic bug - if you alter the 'Normal' cell style to use General number format again it should fix it.

OMG I love you! This works! To go into more detail, you will need to add a "Custom Ribbon" to your toolbar (name it whatever you want, ie "Bug Fix"), change the drop down option of "Choose commands from:" to "All commands", scroll all the way down to the command called "Style" and add it to the toolbar. Next, you will have to select one cell and change the format to "General", then click on the new "Style" command button, choose "Normal" which should already be selected, it will ask you "Redefine 'Normal' based on selection?" and choose Yes. Hope this guide helps!

 

[dorothyn]

To expand on Rory's explanation for people like myself who need step-by-step instructions:

In Excel 2010,

Step 1: Click on the Home tab
Step 2: Click on Cell Styles
Step 3: Hopefully, Normal will be selected. If not, select it then right-click on it and choose Modify.
Step 4: Another menu will pop up where you'll see a Format button. Click on it.
Step 5: This should bring up the familiar formatting menu where you can choose General under Category for your formats.

This should default your format for your workbook to General.

 

[RoryA]

If you're lazy like me, you can also put this into your Personal Macro Workbook:

Sub ResetNormal()
    ActiveWorkbook.Styles("Normal").NumberFormat = "General"
End Sub

and run it when needed.

 

[DocAElstein]

I use Excel 2010 (and 2007) in German and English. Sometimes I get annoying similar problems that are probably bugs: - I can have in two separate cells exactly the same date, Date1 and Date2 (or currency value) which in every way I can think of (settings cell format etc. etc…) look exactly the same. But a VBA code will not recognize them as the same in any match or comparison that I do!!??. A trick I found was to compare Date1=Cstr(Date2) and it then usually works. No idea why
… sometimes then the dates change randomly in format as well as described here in this thread. The general conversion usually helps that one.. but converting to Text seems to work better for me. In code I liberally throw in lines like
Cells(r,c) .NumberFormat = "@"
Which converts to text. That usually keeps things fairly stable (for a while!)