Advanced filter by DATE

[Vince_Lunetta_1962]

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

[#2]

@Vince_Lunetta_1962 

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.

[#3]

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

[#4]

@Vince_Lunetta_1962 

If we speak about Advanced Filter it works such way

[#5]

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.

[#6]

@Vince_Lunetta_1962 

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.

[#7]

@Vince_Lunetta_1962 

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:

[#8]

@Sergei Baklan 

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?

[#9]

@Vince_Lunetta_1962 

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)