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.

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 big reason for the pivot table date filter not working is the group dates option in the auto filter menu is disabled. This option status is found in the Advanced tab from the Excel Settings.

Pivot Table Date Filter Not Working Issue: 2 Solutions

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 did not work 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 by 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 from adding 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.

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.

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.

• Subsequently, 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.

Download Practice Workbook

You can download our practice workbook from here for free!

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.

Related Articles

• How to Create a Timeline in Excel to Filter Pivot Table
• How to Use Pivot Table to Filter Date Range in Excel