Excel Pivot Table - Date Range Filter

Question

I'm pretty new to Excel. I've created a pivot table and everything works how I want it to except for one thing, the date filter. By default by using the report filter, all of the dates pulled from my spreadsheet are in the dropdown list and they have to be checked individually to filter the table.

I was wondering if there is a work around to be able to filter the table using a date range, exactly how it can be done using the date filter in the spreadsheet. Why I want to be able to do is filter my pivot table by month rather than having to manually select all of the dates in the given month. Can this be accomplished using VBA?

Answer 1

I was able to get this done without writing any code. It turned out to be quite simple. In order to get more functionality out of my date filter, I temporarily moved the date field from the report filter to the row labels. From here I grouped the date field by months and then just moved it back over to the report filter.

Answer 2

Excel has pretty robust built in date filter options on pivot tables(which version of excel are you using):

Good Luck.

EDIT:

Make your pivot table dynamic. This macro will update your pivot table:

Sub UpdatePivotTable()
    Sheets(1).PivotTables(1).RefreshTable
End Sub

You can edit/sort/filter your initial data however you want.

EDIT2: So you might be able to make use of something like this - then you can just autofill x's into the dates that you want - and then run the pivot table update macro:

Good Luck.