Jul 20 2021 06:13 AM
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 |
Jul 20 2021 06:32 AM
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.
Jul 20 2021 07:22 AM
Jul 20 2021 08:52 AM
Jul 20 2021 10:53 AM
Jul 20 2021 12:00 PM
Advanced Filter is not updated automatically. You'd need VBA code for that.
If you would like help with that, it'd be useful if you could attach a small sample workbook.
Jul 20 2021 01:19 PM
One way is VBA, on each change of any cell in criteria range it updates the filter. I believe @Hans Vogelaar 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:
Jul 21 2021 04:56 AM
Thanks for your responsiveness. I laughed when you mentioned VARIANT in light of the COVID stuff.
Is $G$2 a variable that limits the lookup? If I type a month in that location, the filter 'runs' and returns all those in July. Is that correct?
Jul 21 2021 03:19 PM
In G2 is just month number for criteria formula, you may hardcode month number directly into formula.
If to copy into another sheet the workaround is here How to copy data to another worksheet with Advanced Filter in Excel? (extendoffice.com)