I am running into a problem many others have run into before, including myself, but this time nothing at all seems to work... The problem of dates not automatically grouping into years, months, days, etc. in the filter dropdown of a pivot table.

The symptoms: any pivot table, whether imported data or manually entered, will refuse to group a date field in the filter into years, months, weeks, etc. This happens on two machines now, using both hand-entered dates and exported string-dates, and no amount of formatting makes it change. The odd thing is, the dates are clearly treated as dates, because the table autofilter does group them as you would expect, it's only the Pivot Table that refuses to do so. What's more, I can use them in a Timeline, and even the "Date Filters" menu is available and I can filter for "Last Month" and what have you, it's just the actual hierarchy isn't displayed like it is in the Table.

What I've tried, to no avail:

  1. Messing with the ActiveWindow.AutoFilterDateGrouping option. This only affects the Table filter.
  2. The usual Text-to-Columns trick, although obviously I also tried just manually entering raw tables with dates.
  3. Linking the table to PowerPivot and basing the pivot table on that.
  4. Repairing the install.
  5. Fiddling with the number format in the Pivot Table itself, although not with much forethought.

The only thing I can suspect are my regional settings, but that's a huge stretch. I run unusual regional settings in that I like my dates YYYY-MM-DD, and this may be causing some sort of conflict. It shouldn't, but maybe. The existence of this issue in identical form on two separate machines would indicate that either I'm doing something wrong, or I just found a bug in Excel 2013.

Related: These images are what I want to see in the Pivot filter but only see in the Table filter.

I'm at my wit's end, I've tried everything I can think of, and given that this error happens with bone-standard, hand-entered dates I'm completely stumped. Help?

Edit: It occurs to me after reading this article that this may only be a feature in PowerPivot somehow, but I could swear I never did what that article describes and I've definitely used this feature.

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.