Dates in Pivot Table

[gkisystems]

Does anybody know why (in Excel 2010) sometimes the pivot table will show various dates in the drop down menu as MM/DD/YYYY while other times it will show dates as just the Years with a little plus box that then expands to the months with another plus box that expands to the days?

I can't figure out why I get it sometimes one way and other times the other way. More importantly, how do I get MM/DD/YYYY to automatically group by year and by month? I know you can force it to do it manually with data grouping, but I want to know how to activate the automatic/easy way that comes built-into Excel. Any ideas?

 

[gkisystems]

Just to clarify, my dates automatically group in the autofilter of my data table because I have the option selected (File-->Options-->Advanced-->Display options for this workbook-->Group dates in the AutoFilter menu). I need it to work the exact same way when I have the dates in a pivot table - but I see no option to turn this on for the pivot table. Any help is appreciated!

 

[mole999]

just a thought, what are you column formats, maybe they control the view

 

[gkisystems]

just a thought, what are you column formats, maybe they control the view

The entire column in which the pivot table (date field) is located is formatted as a date. The source data is also formatted as a date.

When I "trick" my pivot table column headers to use the autofilter instead of the filter that normall comes with the pivot table, the dates automatically consolidate in the autofilter, but that's not the way I'm looking to go if possible.

Note - you can put an autofilter on your pivot table if you click on the cell immediately to the right of the headers on your pivot table and push the autofilter button (you cannot push the autofilter button when your mouse is inside the actual pivot table).

 

[Excelaron]

Did you try clicking the date field in the pivot table then click Options -> Group Field -> select both Year and Month

 

[Excelaron]

Or just Year and Day

 

[gkisystems]

Yes, but the trouble is not that I want the grouping in the pivot table itself - but rather ONLY in the filter. I only want one column for the date (not multiple columns for Year, Month, Day).