Excel Pivot Table Date Filter Not Working (2 Possible Solutions)

A pivot table is a fantastic tool to summarize and visualize a large dataset. The pivot table has dynamic filtering options and has a scope to apply dynamic formulas very easily. But, it may occur sometimes, that your pivot table date filter is not working. This may happen for several reasons. In this article, I will discuss those reasons and show you all the possible fixes to make it work.

Download Practice Workbook

You can download our practice workbook from here for free!

Reasons for Pivot Table Date Filter Not Working

There are mainly 2 reasons for which the pivot table date filter doesn’t work. Such as:

1. If All Cells of a Column Are Not in Date Format

One of the most common reasons for the pivot table date filter not working is all the data are not in the proper date format. It may look like a date from a quick view, but still, it can be actually in the text format.

2. If AutoFilter Grouping Date Option Is Not Enabled

Another biggest reason for the pivot table date filter not working is the group dates option in the auto filter menu is disabled. This option status s found in the Advanced tab from the Excel Settings.

2 Solutions to Pivot Table Date Filter Not Working Issue

Say, we have a dataset of 5 days’ dates, sales, and profits.

Next, we have created a pivot table according to this dataset.

But when we tried to apply the date filter in the pivot table it is not working properly.  Now, you can apply mainly 2 possible solutions to fix the pivot table date filter not working.

1. Ensure Date Format for Entire Column

In this problem, you can see, that if we filter the date values for this month, one data gets missing, even though, all the dates are from this month. We can fix this problem following any of the two ways described below.

1.1 Fix It Manually

Follow the steps below to manually check and fix the date formats to fix this problem.

📌 Steps:

• Initially, click on the B5 cell. Subsequently, go to the Home tab >> Number group >> note the format inside the Number Format text box. The B5 cell’s value is in the Date format.

• Similarly, repeat this step for all the Date column cells. At the B8 cell, you can see that the value is in the Text format.

• Text format impedes the pivot table to add this value in date filtering. So, now, click on the B8 cell >> go to the Home tab >> Number group >> click on the downward arrow inside the Number Format text box >> choose the Short Date option from the listed options.

Thus, you can see, that all the values of your Date column are now in the Date format and now the pivot table data filter will work accordingly.

Read More: How to Filter by Date in Excel (4 Quick Methods)

1.2 Using ISTEXT Function

Now, it might get tiring and time-consuming to manually check the date format of a cell. You can use the ISTEXT function in this regard. Follow the steps below to accomplish this.

📌 Steps:

• At first, create a new column beside the pivot table named the Check Cell Format.

• Now, select the E5 cell and insert the following formula. Subsequently, press the Enter button.

• As a result, you can see, that FALSE is written in the E5 cell. Because, the B5 cell has a date format value, not a text value.
• At this time, place your cursor on the bottom right position of the E5 cell.
• Consequently, the fill handle will appear. Now, drag the fill handle down to the E9 cell.

• As a result, you will see that the Date column cells’ formats are checked if in the text format and shown TRUE/FALSE accordingly. You can see the E8 cell will give the TRUE value. And, you can find that the B8 cell has the text format value.

• Now, to fix it, select the B8 cell >> go to the Home tab >> Number group >> click on the downward arrow in the Number Format text box >> select the Short Date option.

Thus, you can fix the date formats. If you try to filter the pivot table by dates now, you will see all the dates are filtered now successfully.

Read More: How to Filter Date Range in Pivot Table with Excel VBA

2. Enable AutoFilter Grouping Dates

If your problem is not fixed by the first method, then the possibility is that you have a problem in some settings about date filters. You can follow the simple steps below to fix this. 👇

📌 Steps:

•  First and foremost, go to the File tab.

• Subsequent;y, click on More… >> Options

• At this time, the Excel Options dialogue box will appear. Now, go to the Advanced tab.
• Following, tick the option Group dates in the AutoFilter menu. Last but not least, click on the OK button.

Thus, you will see your pivot table will now be able to filter dates properly.

Read More: How to Use Pivot Table to Filter Date Range in Excel (5 Ways)

Conclusion

In a nutshell, in this article, I have shown you how to fix the pivot table date filter if it’s not working. Read the full article carefully and solve your own Excel file’s pivot table problem accordingly. I hope you find this article helpful and informative. You are very welcome to comment here if you have any further questions or recommendations.

And, visit ExcelDemy to learn many more Excel solutions and tips. Thank you!

Related Articles

• How to Use Custom Date Filter in Excel (5 Easy Ways)
• Excel VBA: Filter Date Range Based on Cell Value (Macro and UserForm)
• How to Filter Last 30 Days of Date in Excel (5 Easy Ways)
• Filter Dates by Month and Year in Excel (4 Easy Methods)
• VBA to Pivot Table Filter Between Two Dates in Excel
• How to Filter Date Range in Excel (5 Easy Methods)
• Excel VBA: Filter Date before Today (With Quick Steps)