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

Get FREE Advanced Excel Exercises with 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.

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

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

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

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


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


What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjim Reza
Tanjim Reza

Md. Tanjim Reza Tanim, a BUET graduate in Naval Architecture & Marine Engineering, contributed over one and a half years to the ExcelDemy project. As an Excel & VBA Content Developer, he authored 100+ articles and, as Team Leader, reviewed 150+ articles. Tanim, leading research, ensures top-notch content on MS Excel features, formulas, solutions, tips, and tricks. His expertise spans Microsoft Office Suites, Automating Finance Templates, VBA, Python, and Developing Excel Applications, showcasing a multifaceted commitment to the... Read Full Bio

4 Comments
  1. This still doesn’t work – ONE PC recognize file and dates, another just not.

    • Hello ALEKS,
      Thanks for reaching out to us. Make sure that all the cells in the date column of the Pivot table are in the accurate date format. Only then the date filter will work properly. Moreover, ensure the Excel option mentioned in the second solution of the article is ticked. Hope this helped.
      Reach out to us again for any further queries.
      Regards,
      Aung

  2. Hello,
    I have applied all your steps and it is still not working. This PT was working with data from 2022, 2023 and it stopped working with data from 2024. Help!!

    • Reply Mahfuza Anika Era
      Mahfuza Anika Era Feb 27, 2024 at 11:48 AM

      Hello Andrea!

      You could follow these alternative solutions below to solve your problem:
      Remove any blank cells or non-date entries in the date column. Filter out non-date values and ensure that all cells in the date column contain valid date data.
      Click on the drop-down arrow in the date filter, and check the filtering options. Make sure the desired date range or specific dates are selected. Adjust the filter criteria accordingly.
      Refresh the pivot table to ensure it reflects the most recent changes in your data. Right-click on the pivot table and select Refresh.
      Create a new pivot table and see if the date filter works. If it does, the original pivot table may be corrupted. Copy your settings to the new pivot table or recreate it.
      Ensure that you are using a compatible Excel version for the features you are trying to use. Update Excel to the latest version if possible.
      If your problem is not yet solved, please join our ExcelDemy Forum and post this problem with your Excel file attached to it.

      Regards,
      Nafis
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo