Reposted from Microsoft Community forum by recommendation to this one.

Coming back after holiday break, a key spreadsheet had many numbers in pivot tables formatted as dates. Not sure why, but I have corrected this by modifying the Normal style to General.

Still all filter drop-downs (at the top of a pivot table) contain data that is formatted MMM-YY even though the source data is formatted as a Number, 2 dec places or as a custom date (DD.MM.YYYY). This is true in all cases and in many pivot tables on one sheet. These drop-downs used to work fine. Drop-down filters now display numbers or custom dates as what appears to be a default date format and I don't know how to change the format for a filter drop-down list (it should display as in the source, shouldn't it?). I have tried:

1. Checking the source data column on the source sheet - formatting is as expected.
2. Adding the field to the values to see if it is formatted correctly - formatting is as expected.
3. Adding a slicer for the field to the pivot table - formatting is as expected.
4. Changing the format of the cell containing the filter - no change.
5. Searching everywhere for an answer!

Example

Source data 69377 35840 18340 840 900 900

etc.

Pivot table filter drop-down list Jun-02 Nov-05 Sep-34 Oct-63 Oct-82

etc.

Any ideas or help will be greatly appreciated.

Since you have modified the normal stlye as General, it should works fine now.

You can try to set the cell containing the filter as Normal stlye,uncheck the option 'Preserve cell formatting on update'. Right click a cell->PivotTable Options->Layout&Format,then refresh the pivot table,check the option again.

Thank you for your suggestions. Sadly, I had also tried that sequence of events, but didn't record it above. I just gave it another try on a different pivot table on the same sheet with the same result--no change, numbers still formatted as MMM-YY.

Have you changed your source data before?
If so, you can set the 'Number of items to Retain Per field' as No.
Right click a cell in the pivot table->PivotTable Options->Data

I was so excited--something new I didn't know about and hadn't tried! Sadly, this didn't work either. Thank you for the suggestion, though.

:(

Would you mind to share your workbook?
Remove the sensitive information, upload it into a file share site, then share the link with us.

Thank you very much. I have stripped out everything I can think of and left two examples of the problem (marked by red arrows) on the second tab.

https://skydrive.live.com/redir?resid=4FE3866D8404A103!263&authkey=!AJZdg4O6iej_HmQ&ithint=file%2c.xlsm

Coral

I have the exact same problem so i'm very interested in the answer - its driving me crazy!

I found a strange customer data format in the excel file.

After delete it, the data format changes to general.Give it a try.

• Marked as answer by Tony Chen CHNMicrosoft contingent staff, Moderator Thursday, January 23, 2014 4:51 PM

Thankyou! That solved the pivot table issue. I think it may have also solved the issue of cells defaulting to date format. I appreciate the help.

Yes, thank you! That did it. Crazy. Any thoughts on where those odd formats came from? I found a couple more as well. Can't insert images yet, apparently, so manually placed here:

[$-1009]mmmm-dd-yy

[$-409]h:mm:ss AM/PM

I certainly did not choose to add them!

Thank you again. I really appreciate the help.

Coral

Ran into a similar problem with a date column only showing "Jan" instead of date. Went looking for these nasty custom formats. Googled the format and came up with this:

http://stackoverflow.com/questions/894805/excel-number-format-what-is-409

Still trying to resolve this latest one, but seems this problem has been around for a while.

409 is just the LCID Hex for English - United States.
Open your excel file,go to the format cells window-> date, once you choose one date type and the locale,excel will create a custom format starts with [$...].


In additionally,If you get more issue,I would suggest you open a new thread in this forum,thanks for your understanding.

• Edited by zkandyModerator Wednesday, January 22, 2014 1:43 AM

This also solved my problem(which was such a disgusting one.)

Thank you very much...