Filter for active quarters in a Date Range

So, I set up a lookup table:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	M	N
6	January	Q1
7	February	Q1
8	March	Q1
9	April	Q2
10	May	Q2
11	June	Q2
12	July	Q3
13	August	Q3
14	September	Q3
15	October	Q4
16	November	Q4
17	December	Q4

Sheet: Sheet1

and two reference cells:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	I	J
6	Q1	2022

Sheet: Sheet1

Then I used this:

=LET(q,FILTER(M6:M17,N6:N17=I6),s,DATE(J6,INDEX(MONTH(DATEVALUE(q&" 1")),1),1),f,DATE(J6,INDEX(MONTH(DATEVALUE(q&" 1")),3),31),FILTER(A6:C15,((B6:B15<=s)*(C6:C15>=s))+((B6:B15<=f)*(C6:C15>=f))))

Alternatively, without the lookup table:

=LET(s,DATE(J6,LOOKUP(I6,{"Q1","Q2","Q3","Q4"},{1,4,7,10}),1),f,DATE(J6,LOOKUP(I6,{"Q1","Q2","Q3","Q4"},{3,6,9,12}),31),FILTER(A6:C15,((B6:B15<=s)*(C6:C15>=s))+((B6:B15<=f)*(C6:C15>=f))))

Seen, but no reply, not even a 'thank you for your time and effort'.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

Your time and effort is greatly appreciated - thank you very much for the example.

You only posted about an hour ago and as my actual dataset is a lot more complex than the example I gave, I was trying to implement your suggestion on it before I came back to you, in case I wanted to clarify anything. I was not ghosting you, just needed a bit of time to digest the solution.

I will leave the post active for the moment as I would like to see if anyone has any Power Query ideas.

If you want a PowerQuery solution, then you need to add this to the thread title for better visibility - I missed it completely in your opening post.

In future, if you are going to take time testing a solution, at the very least drop a note to say this so that the helper knows. Thanks.

Here are two possible solutions, both including the following:
1. Make a second range T5:V8 that defines the quarters
2. Populate the quarter being used to filter in T9:V9 using: =P5; =VLOOKUP(P5,T5:U8,2) and =VLOOKUP(P5,T5:V8,3) respectively
3. Convert the range A5:C15 into an Excel table
The first proposed solution shown in P6:R15 displays the filtered dates using:

Formula:

Please Login or Register  to view this content.

Note that StartQtr is cell U9 and EndQtr is cell V9
The second proposed solution adds a Filter column (D5:D15) to the original table. The Filter column is populated using: =AND([@[Start Date]]<=EndQtr,[@[End Date]]>=StartQtr)
When TRUE is selected using the sort/filter down arrow in cell D5 the table is filtered.
Let us know if you have any questions.

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.