Excel 2013 pivot table filter drop-down numbers now formatted as dates
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:
- Checking the source data column on the source sheet - formatting is as expected.
- Adding the field to the values to see if it is formatted correctly - formatting is as expected.
- Adding a slicer for the field to the pivot table - formatting is as expected.
- Changing the format of the cell containing the filter - no change.
- 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.
January 8th, 2014 9:28pm
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.
January 9th, 2014 7:47am
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.
January 9th, 2014 3:53pm
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
January 10th, 2014 7:49am
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.
:(
January 10th, 2014 5:00pm
Would you mind to share your workbook?
Remove the sensitive information, upload it into a file share site, then share the link with us.
January 14th, 2014 8:44am
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
January 15th, 2014 6:29pm
I have the exact same problem so i'm very interested in the answer - its driving me crazy!
January 15th, 2014 11:36pm
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
January 16th, 2014 3:02am
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.
January 16th, 2014 5:42am
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
January 16th, 2014 3:40pm
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.
January 21st, 2014 10:03pm
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
January 22nd, 2014 1:43am
This also solved my problem(which was such a disgusting one.)
Thank you very much...
March 9th, 2015 7:33am