Excel Pivot Table Date Filters

In a pivot table, you can use date filters to show the results for a specific date or date range

Introduction

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:

• Individual date checkboxes
• Selection by specific date range
• Dynamic date range selection

Filter With Date Checkboxes

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:

1. Click the drop down arrow on date field
2. To show the check boxes, add a check mark to "Select Multiple Items"
3. In the list of dates, add check marks to show dates, or remove check marks to hide dates.
4. Click OK

Clear a Date Filter

To remove a date filter from a pivot table field:

1. Click the drop down arrow on the field heading
2. If necessary, select the Field name from the drop down list (this step might be necessary for Row Labels, in Compact layout)
3. Click Clear Filter From [date field name]

Filter for a Specific Date Range

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.

1. Click the drop down arrow on the Row Labels heading
2. Select the Field name from the drop down list of Row Labels fields
3. Click Date Filters, then click Between…

   

4. In the Between dialog box, type a start and end date, or select them from the pop up calendars.
5. In Excel 2013 and later, there is a "Whole Days" option. Check that option to ignore times that are stored in the date cells.
6. Click OK.

   

Filter for a Dynamic Date Range

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.

1. Click the drop down arrow on the Row Labels heading
2. Select the Field name from the drop down list of Row Labels fields
3. Click Date Filters, then click This Month

   

Date Filters in Report Filter Area

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.

Date Filters in Report Filter Area

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.

Video: Pivot Table Date Filters

To see the steps in action, please watch this short video on Pivot Table Date Filters.

Download the Sample File

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.

More Tutorials

FAQs - Pivot Tables

Pivot Field Multiple Filters

Pivot Table Introduction

Grouping Data

Multiple Consolidation Ranges

Running Totals

Summary Functions

Clear Old Items in Pivot Table