Forum Discussion
Advanced filter by DATE
Does Advanced filter allow for DATES by Month only. I'd like to enter a month in a cell and auto filter the table for all data points with the month regardless of year.
This is the table:
| 94 | 1927.07.01 | 7/1/1927 |
| 90 | 1931.05.01 | 5/1/1931 |
| 57 | 1964.07.01 | 7/1/1964 |
| 82 | 1939.02.01 | 2/1/1939 |
| 75 | 1946.05.01 | 5/1/1946 |
8 Replies
- Thx Sergei. Still will not work. When Criteria range is modified, isn't the filter automatic? The reapply icon is grayed out in my workbook. I had it work once, changing from Jul to Aug and the text AUG disappears after the update. I'm trying to make the table useful to a group easily look up a date with a simple choice.
One way is VBA, on each change of any cell in criteria range it updates the filter. I believe HansVogelaar could suggest you perfect variant if concrete sample exists.
Another way to refresh is to click on Advanced Filter button when Ok, it remembers your previous setting.
And if you are on Excel 365 you may use FILTER() function:
Let's say the dates are in C2:C100.
If necessary, insert an empty column in column D.
Enter the word Month in D1.
In D2, enter the formula =MONTH(C2) and fill down to D100.
You can now easily use AutoFilter or Advanced Filter to filter the data by month.
- Hans THX.
Helps however I want a user to enter data under Enter MONTH (can be a 7 for July or a list of months) then for filter to screen for only those months.
Age Date of Birth By month Month Enter MONTH
94 1927.07.01 7/1/1927 7
90 1931.05.01 5/1/1931 5
57 1964.07.01 7/1/1964 7
