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.

Sample Dataset

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

Pivot Table to Fix Pivot Table Date Filter Not Working

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.

Note the Format of B5 Cell

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

B8 Cell has 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.

Make the B8 Cell as Date Format to Fix Pivot Table Date Filter Not Working

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.

New Column of Pivot Table

  • Now, select the E5 cell and insert the following formula. Subsequently, press the Enter button.
=ISTEXT(B5)

Insert Formula to Check if B5 Cell is in Text Format

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

Drag Fill Handle to Copy Same Formula

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

B8 Cell has Text Format

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

Change the B8 Cell Format to Fix the Pivot Table date Filter Not Working

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.

Access the File Tab

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

Access the Options Window to Fix Pivot Table Date Filter Not Working

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

Tick the Following Option to Fix Pivot Table Date Filter Not Working

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


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!

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo