NETWORKDAYS & Holidays with Criteria

[zero269]

Hello,
I'm currently using the following formula to return the number of Weekdays between two dates, excluding dates from another Table.

=NETWORKDAYS( [@[Test Date]], t_Period_Active[End Date], t_Event_Dates[[Start Date]:[End Date]])

However, this table has dates that meet two different criteria: Events and Breaks. The ~Breaks indicate No School, while ~Events are open School days.

I'm trying to figure out how to add criteria to the Holiday portion of the formula to only exclude dates where the Category contains "Break".

Any help would be greatly appreciated. Here's my Sample Data using xl2bb:

testdates.xlsx
	A	B	C	D
1	Start	End	Category	No School
2	2024-08-16	2024-08-16	School Event
3	2024-08-21	2024-08-21	School Event
4	2024-09-02	2024-09-02	Holiday Break	1 day
5	2024-09-11	2024-09-11	School Event
6	2024-09-26	2024-09-30	School Break	3 days
7	2024-11-28	2024-11-29	Holiday Break	2 days
8
9	Testing Ends
10	2024-12-21
11
12	Test Date	Test Days
13	2024-08-16	83
14	2024-08-21	81
15	2024-09-11	68
16	2024-10-01	57
17	2024-12-18	3
Sheet1

Cell Formulas
Range	Formula
B13:B17	B13	=NETWORKDAYS( [@[Test Date]],$P$15,t_SchoolSchedule[[Start]:[End]])

 

[zero269]

OK, so I THINK I might have gotten it to work adding a FILTER function. An idea from this video.

=NETWORKDAYS( [@[Test Date]],t_Period_Active[End Date],FILTER(t_Event_Dates[[Start Date]:[End Date]], ISNUMBER(SEARCH("break",t_Event_Dates[Category]))))

I just need to test it against a smaller dataset, but when I added a fake Holidy Break for July 16, I noticed JUL 16 & 17 now show 22 days in my original dataset. So that's good news for now.

 

[zero269]

Here's my test using my original Sample Data:
Looks good to me I can see the Test Days increasing 3 days to start; 3 Event Dates.
However, I'm am curious if this is truly the best approach and if anyone uses a similar solution.
Thanks...

testdates.xlsx
	A	B	C	D
1	Start	End	Category	No School
2	2024-08-16	2024-08-16	School Event
3	2024-08-21	2024-08-21	School Event
4	2024-09-02	2024-09-02	Holiday Break	1 day
5	2024-09-11	2024-09-11	School Event
6	2024-09-26	2024-09-30	School Break	3 days
7	2024-11-28	2024-11-29	Holiday Break	2 days
8
9	Testing Ends
10	2024-12-21
11
12	Test Date	Test Days	With Filter
13	2024-08-16	83	86
14	2024-08-21	81	83
15	2024-09-11	68	69
16	2024-10-01	57	57
17	2024-12-18	3	3
Sheet1

Cell Formulas
Range	Formula
B13:B17	B13	=NETWORKDAYS( [@[Test Date]],$P$15,t_SchoolSchedule[[Start]:[End]])
C13:C17	C13	=NETWORKDAYS( [@[Test Date]],$A$10,FILTER(t_SchoolSchedule[[Start]:[End]], ISNUMBER(SEARCH("break",t_SchoolSchedule[Category]))))