Advanced filter by DATE

%3CLINGO-SUB%20id%3D%22lingo-sub-2565774%22%20slang%3D%22en-US%22%3EAdvanced%20filter%20by%20DATE%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2565774%22%20slang%3D%22en-US%22%3E%3CP%3EDoes%20Advanced%20filter%20allow%20for%20DATES%20by%20Month%20only.%20I'd%20like%20to%20enter%20a%20month%20in%20a%20cell%20and%20auto%20filter%20the%20table%20for%20all%20data%20points%20with%20the%20month%20regardless%20of%20year.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20the%20table%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22377%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3E94%3C%2FTD%3E%3CTD%3E1927.07.01%3C%2FTD%3E%3CTD%3E7%2F1%2F1927%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E90%3C%2FTD%3E%3CTD%3E1931.05.01%3C%2FTD%3E%3CTD%3E5%2F1%2F1931%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E57%3C%2FTD%3E%3CTD%3E1964.07.01%3C%2FTD%3E%3CTD%3E7%2F1%2F1964%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E82%3C%2FTD%3E%3CTD%3E1939.02.01%3C%2FTD%3E%3CTD%3E2%2F1%2F1939%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E75%3C%2FTD%3E%3CTD%3E1946.05.01%3C%2FTD%3E%3CTD%3E5%2F1%2F1946%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2565774%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2565888%22%20slang%3D%22en-US%22%3ERe%3A%20Advanced%20filter%20by%20DATE%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2565888%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F745947%22%20target%3D%22_blank%22%3E%40Vince_Lunetta_1962%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20the%20dates%20are%20in%20C2%3AC100.%3C%2FP%3E%0A%3CP%3EIf%20necessary%2C%20insert%20an%20empty%20column%20in%20column%20D.%3C%2FP%3E%0A%3CP%3EEnter%20the%20word%20Month%20in%20D1.%3C%2FP%3E%0A%3CP%3EIn%20D2%2C%20enter%20the%20formula%20%3DMONTH(C2)%20and%20fill%20down%20to%20D100.%3C%2FP%3E%0A%3CP%3EYou%20can%20now%20easily%20use%20AutoFilter%20or%20Advanced%20Filter%20to%20filter%20the%20data%20by%20month.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2566134%22%20slang%3D%22en-US%22%3ERe%3A%20Advanced%20filter%20by%20DATE%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2566134%22%20slang%3D%22en-US%22%3EHans%20THX.%3CBR%20%2F%3EHelps%20however%20I%20want%20a%20user%20to%20enter%20data%20under%20Enter%20MONTH%20(can%20be%20a%207%20for%20July%20or%20a%20list%20of%20months)%20then%20for%20filter%20to%20screen%20for%20only%20those%20months.%3CBR%20%2F%3EAge%20Date%20of%20Birth%20By%20month%20Month%20Enter%20MONTH%3CBR%20%2F%3E94%201927.07.01%207%2F1%2F1927%207%3CBR%20%2F%3E90%201931.05.01%205%2F1%2F1931%205%3CBR%20%2F%3E57%201964.07.01%207%2F1%2F1964%207%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2566493%22%20slang%3D%22en-US%22%3ERe%3A%20Advanced%20filter%20by%20DATE%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2566493%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F745947%22%20target%3D%22_blank%22%3E%40Vince_Lunetta_1962%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20we%20speak%20about%20Advanced%20Filter%20it%20works%20such%20way%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20509px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F297082i690E09B025072B8E%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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: 

 

941927.07.017/1/1927
901931.05.015/1/1931
571964.07.017/1/1964
821939.02.012/1/1939
751946.05.015/1/1946
8 Replies

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

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

@Vince_Lunetta_1962 

If we speak about Advanced Filter it works such way

image.png

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.

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

@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:

image.png

@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?

 

@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)