Format continues to change to Date

[Jon Lester]

I currently have a File with 3 Data Pages ranging from 5K to 18K lines of Data. Off of these I have 8 pages of pivot tables and charts with 4-9 Charts per page.
Problem is that the file changes it' formats to Date. When I go through and change everything except Date columns to general the Pivot table Drop lists still are displaying a Date Format and I can’t change them making determining the selection a trial by error issue.
Why do I lose the format? Last month I completely pulled down all data and rebuilt the file because of this and it has resurfaced again.

 

[RoryA]

There are several mentions of this in various forums and newsgroups, but to my knowledge there is no fix published yet. It seems to happen most often to shared workbooks but also sometimes to regular ones, often those with lots of pivot tables in from what I've read (haven't had it yet myself). The only 'fix' I know of is to reset the workbook's Normal style to General rather than the Date format it has probably become.

 

[cboss22]

I am encountering the same problem with Excel 2010. I do not share the file, but I do have a number of pivot tables and loads of formatting. I also encounter an error regarding too many formats. I'm not sure if that has anything to do with it, but I was wondering if anyone has seen that error in conjunction with the format changing to date?

 

[KBSpec]

I'm having the same problem with a 2010 file. I don't have pivot tables, but I use the general format option a lot. It seems that whenever anyone tries to open the file when it is already in use, the formats change to a date format. Would it be helpful to change the general formats to text? It has been a long time since this was posted. Has a solution been discovered?

 

[Peter_SSs]

Take a look at this article. It is written about Excel 2007 but I suspect you may have the same issue.

 

[KBSpec]

Thank you for the link. I had tried changing the Normal cell format to general. That did not help. I have recreated my document from scratch. I removed all the merged cells in the header section and specified as many formats as number and text as I could. There are only a few column that are still general. Hopefully, these changes will be enough to stop the format flip flopping.

The new spreadsheet is being utilized as we speak. It has successfully survived today! I'm going to wait a week before I declare victory. In the past, the problem happened pretty frequently - going no more than a couple of days without the format change. I'll post back to let you know if these changes helped or not.

Thanks again.

 

[KBSpec]

The new spreadsheet continues to have problems. It was limited to date formats changing to a different date format. I've since changed to a single date format to see if that will stabilize the file. I'll report back on the success of this change.

 

[oferav]

Hi all,
Have you found a solution for this problem? Normal style keeps changing the format from general to currency automatically and I'm about to lose my mind over it.
TNX

 

[twfreeman]

Hi all,
Have you found a solution for this problem? Normal style keeps changing the format from general to currency automatically and I'm about to lose my mind over it.
TNX

I've been having this problem myself, and after Googling it didn't turn up an answer, I just tried recording the process of resetting the style. I haven't tried this yet, but you could load it with an Open Event Sub.


With ActiveWorkbook.Styles("Normal")
.IncludeNumber = True
.IncludeFont = False
.IncludeAlignment = False
.IncludeBorder = False
.IncludePatterns = False
.IncludeProtection = False
End With
ActiveWorkbook.Styles("Normal").NumberFormat = "General"


Obviously if you want something else, such as Number, or w/e, you'll have to make changes accordingly.

 

[TCEMR]

I have a large workbook that spontaneously changed all of my "General" cells to "Time" with the format of: [$-409]m/d/yy h:mm AM/PM;@ Just HAPPENED a few weeks ago. No clue why. I found a brilliant response on this forum but I can't find it again to thank the guy. I just spent much of a weekend writing VBA code to fix all of my corrupted cells and then stumbled on this SIMPLE solution. Even the MS Excel forum site doesn't mention this...

On any sheet, click into a cell. Click "Format Cells" on the Home tab, then "Custom". Look for any format types that contain: [$-409]m/d/yy h:mm AM/PM;@ Delete them, and VOILA! Every non-formatted cell on every sheet will be fixed, without affecting cells that you've specifically formatted otherwise!!! You may need to unprotect all of your sheets first if they are password protected.