In a pivot table, you can use date filters to show the results for a specific date or date range
In a pivot table, you can use date filters to show the results for a specific date or date range. There are three types of date filters:
If a date field is in the Report Filter area, only the date check boxes are available. If you want to filter for a date range, move the field to the Row or Column area instead.
To select specific dates:
To remove a date filter from a pivot table field:
For a date field in the Row or Column area of the pivot table, you can select a specific date range for the filter. This option is not available for Report Filters.
A dynamic date range changes automatically, based on the current date. For example, "Next Week" represents a different range of dates, every day that you open the pivot table file.
If a date field is in the Row or Column area, follow these steps to show the current month's data, as a dynamic date range. This option is not available for Report Filters.
For date fields in the Report Filters area, only the Checkbox filter type is available. This video shows how to move the filter to the Row area, where dynamic filters are available. Then, collapse the date field, so only the heading is available, and not the list of dates.
Unfortunately, the Date Range filters and Dynamic Date filters aren't available in the Report Filters area. For date fields in the Report Filters area, only the Checkbox filter type is available.
If you move a filtered date field from the Row Labels area to the Report Filters area, any filtering will be discarded, unless the checkboxes were used to select specific dates.
To see the steps in action, please watch this short video on Pivot Table Date Filters.
To experiment with the date filters, you can download the zipped sample file. The file is in xlsx format, and does not contain any macros.
To save time when building, formatting and modifying your pivot tables, use the tools in my Pivot Power Premium add-in. With just a few clicks, you can:
and much more!
Last updated: January 18, 2018 3:20 PM