Filters (or AutoFilters) are very powerful in Excel: Not only allow them the basic filtering, but also sorting, filtering by colors and much more. One aspect about the filters I like the most: Gaining a quick overview of the data in the column below. When it comes to dates, filters group dates by year, month and day. That’s very helpful for gaining an overview of the date ranges as well as quickly applying filters. But what, if the filter has suddenly stopped to grouping dates? Here are possible reasons and how to fix it!
Initial comments to the grouping dates issues in filters
A few comment before we start:
- The following reasons are listed in the order of how simple and fast it is to apply them. Typically, the last reason is most probably, but before we start a larger investigation, let’s make sure that the “quick fixes” are finished.
- If some dates are grouped and some are not in your filters, jump right to reason 3 below.
- If you want to know more about filters, please refer to this article.
Reason 1: Grouping dates in filters is disabled
The easiest thing to check if is grouping dates is activated within the Excel options.
- In Excel, go to File.
- Click on Options (usually in the left bottom corner of the screen).
- Go to the Advanced tab in the left pane of the Options window).
- Scroll down to the workbook settings and set the check at “Group dates in the AutoFilter menu”.
Back in your filter, is the grouping working now?
Reason 2: Your filter is not covering all rows to group dates
This second option is more like a quick side note: Make sure that your filter covers all items in your list or table.
To be sure, why don’t you remove it and insert it again? Here is how to do that.
Do you want to boost your productivity in Excel?
Get the Professor Excel ribbon!
Add more than 120 great features to Excel!
Reason 3: Excel does not recognize the dates as dates
Unfortunately, to my experience this is the most common reasons when Excel does not group dates in filters any longer. Excel does not recognize the dates as dates.
You could check this first: What does the summary in the status bar show when you select multiple (more than one, two are enough) date cells?
- If it only say “Count: 2” it’s most probably a text cell (see screenshot on the right side). In this case you have to convert the cells to dates. Here is how to do that (see the section of forcing text to number formats or the universal format at the end).
- If the status bar also shows a summary of the dates (with at least one of the following values: minimum, maximum, sum, numerical count or average), both selected cell are recognized as dates. In this case you should not have any problems (at least with these two cells) and check reasons 1 and 2 above.
I have found other type of problem when I tried to solve a problem in which Column A (with header Name) has seven names and Column B (with header Birthdays). In Column I put 5 random names and in Column B I put 5 random Birthdays. Then I add filter. My task was to find out Birthdays in last five years. I made one cell as today’s date (used =Today()) and one cell showing date five years ago from today’s date (used = EDATE(A2, -60)). But I can’t found auto filter having ‘between filter’ and date listed in filter dropdown shows without full dates, i.e. 2021.., 2022.., 2019, 26/02/2011, 16/10/2017, 25/9/2016, 25/06/2013 instead of 3/5/2021, 5/12/2022, 7/5/2019, 26/02/2011, 16/10/2017, 25/9/2016, 25/06/2013.
I am organizing a classroom of students. I have the students and their information on one worksheet while the 2nd worksheet displays birthdays in a cute way that I can print out. I am filtering using the filter formula and NOT the filter button. When I input a month (I’m using a drop down box) i.e. February or March or any month everything works perfectly. EXCEPT January. I can even change the January birthday to another month, say March, and it will display that birthday for March just fine. Ex. 1-7-2012 –> 3-7-2012. I have checked for merged cell, skipped rows, hidden columns or rows and just about everything I can find as a fix and it still doesn’t work. I have even erased everyone on my worksheet and only have one row input with a January birthday and it still does not diplay my student’s name.
HOWEVER if I do not use the drop down box and type in the month, it works.
What is going on?
My filtered spreadsheet does not recognise dates because of reason 3 (Excel does not recognize the dates as dates) but the status bar shows a summary of the selected dates. “In this case you should not have any problems” but i still do, so how do I fix it?
I have done all the above and it turns out some of my dates are not recognised by excel. I selected the columns with the dates required and changed it to the short date format. Unfortunately, it still will not allow me to filter the whole column by Year, Month and Day 🙁