Excel 2010 Pivot Table - No date range filter option for a report filter?

BLEIP used Ask the Experts™
Hello.  I have a spreadsheet containing data for which I am trying to create a distribution table (Pivot Table) on to display counts.  One of the fields is a date field.  I need to be able to filter the date to display only a specific year and quarter based on user selection.  If I add the date field as a column label, I have the option to use a Date Filter and choose between x and y dates.  However, I want to add the filter as a report filter so the user can simply change the date as required.  Unfortunately, when the field is added as a report filter, I only have the option to select one or many individual dates.  There is no between x and y date option.  Any other ways other to achieve what I am trying to accomplish without creating year and quarter columns in the underlying data source?

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Awarded 2010

in the pivot table, with the dates visible, right-click any  date and then click Group. Click Years, Months and Days and OK.

The data is now grouped dates and two new fields are in the pivot table fields list. You can move Years and Months into the report filter.

With Excel 2010 you can create slicers on Years and Months, with Excel 2013 you can also add a time line. Both slicers and timeline allow multiple selections.

In Excel 2010 I like to arrange my year and months slicers in several columns so they look like tabs.
slicers like tabs
cheers, teylyn
Here is the VBA code that automatically group the dates in your pivot

Sub Groupdate()
Dim PT As PivotTable

Set PT = ActiveCell.PivotTable
Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, _
        False, True, False, True)
   PT.RowAxisLayout xlTabularRow
   With PT.PivotFields("Years")
        .Orientation = xlPageField
        .Position = 1
    End With
End Sub

Open in new window

Please see attached example, Select one of the cell that has date and then click on the macro button to get the desired output.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial