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

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?

Thanks
BLEIPAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

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
Excel amusantCommented:
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.
Group-Date.xlsb

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.