Excel 2013 pivot filter not grouping dates

1
votes

I'm having the following issue using Excel 2013:

My source data is organized as a table. I have a date column titled E-Mail-Datum, formatted as date in dd.mm.yyyy.

Applying the regular filter directly to the source data table nicely groups the dates together as expected, such that one needs to expand year and month within the filter overlay in order to choose a specific date.

But when I create a pivot table on this source data table, I would expect the same when filtering for the same dd.mm.yyyy-formatted date column. This is not the case: The filter overlay shows every single date in a simple (pretty Long) list, not grouping by month/year at all.

How can I get the pivot filter to group dates by year/month?

3
exceldatefilterpivot-table
When you right click on the dates field in the pivot what options are you presented with? – QHarr
@Stefan Klocke: Did my explanation below address your question? – jeffreyweir

3 Answers

0
votes

I'm pretty sure that this is just the way it is...the Tables autofilter automatically groups those dates, but the PivotTable filter doesn't. You can always use the Group Field option from the PivotTable Tools > Analyze tab to group by Year and Month, so that you have separate fields for both, if that helps.

0
votes

Here is a workaround, but not a fix,
Create a new column called day and give it the formula

  • =DAY([@Date]) (if you have created set the data as a table)
  • =Day(A1) (if it is normal data)

Then use Day instead of Days in the pivot table If you don't know how to do this here are the instructions Drag Date down to columns Go to a date field in the pivot table --> right click --> group

  1. Select Years, Month, Days
  2. Drag Years and Month to the filter box
  3. Drag Days out of the pivot table to the selection box
  4. Drag Day into the filter box replacing "Days" with "Day"
0
votes

I think I had a similar problem. The date data looked good in the column, but was not being recognized as a date in the Pivot Table, so Date grouping (e.g. Month, Year) was not available. The solution for me was to change the Number Format to Text (in the main spreadsheet), then change it back to Date. After that, Date grouping options were available.