Count unique dates from multiple date ranges if with start and end dates

[Huey72]

Good morning everyone. I'm looking for a way to count the unique delay days from multiple date ranges, when the dates fall within the start and end dates order date to ready to ship date as period 1 or ready to ship date to delivery date as period 2. Sometimes the delay dates in each of the sequences overlap, so I only want to count each date once, and sometimes the dates in the sequences fall outside the date range from the start and end dates, so I also want to exclude counting any dates that fall outside of that date range.

Here are two examples with expected results for delay days 1 and delay days 2.

Order Date	Ready to Ship Date	Delivery Date	Delay 1 Start Date	Delay 1 End Date	Delay 2 Start Date	Delay 2 End Date	Delay 3 Start Date	Delay 3 End Date	Wait 1	Wait 2	Delay Days 1	Delay Days 2
2019-08-02	2019-09-10	2019-10-30	2019-08-01	2019-08-05	2019-08-04	2019-08-10	2019-09-22	2019-09-23	30	48	8	3
2019-08-06	2019-09-09	2019-10-20	2019-10-15	2019-10-18	2019-10-17	2019-10-21			34	36	0	5

For the first record: the first delay period includes (2019-08-01, 2019-08-02, 2019-08-03, 2019-08-04, 2019-08-05), the second includes (2019-08-04, 2019-08-05, 2019-08-06, 2019-08-07, 2019-08-08, 2019-08-09, 2019-08-10), and the third includes (2019-09-22, 2019-09-23). For delay days 1, I want to count only the unique delay days that fall between the order date and the ready to ship to date (including the dates matching the order date, but up to the day before the ready to ship date); this would leave me with 2019-08-02, 2019-08-03, 2019-08-04, 2019-08-05, 2019-08-06, 2019-08-07, 2019-08-08, 2019-08-09 for Delay Days 1 = 8. For delay days 2, I want to count only the unique delay days that fall between the ready to ship to date and the delivery date (including the dates matching the ready to ship to date, but up to the day before the delivery date); this would leave me with 2019-08-10, 2019-09-22, 2019-09-23 for Delay Days 2 = 3.

For the second record: the first delay period includes (2019-10-15, 2019-10-16, 2019-10-17, 2019-10-18) and the second includes (2019-10-17, 2019-10-18, 2019-10-19, 2019-10-20, 2019-10-21). For delay days 1, I want to count only the unique delay days that fall between the order date and the ready to ship to date (including the dates matching the order date, but up to the day before the ready to ship date); no delay dates match this criteria as they are after the ready to ship date, for Delay Days 1 = 0. For delay days 2, I want to count only the unique delay days that fall between the ready to ship to date and the delivery date (including the dates matching the ready to ship to date, but up to the day before the delivery date); this would leave me with 2019-10-15, 2019-10-16, 2019-10-17, 2019-10-18, 2019-10-19 for Delay Days 2 = 5.

I'm struggling to get a formula or udf to do this count. The best I can do so far is use the sequence function to get the list of dates for each of the delay periods, but I can't figure out how to use them to create one unique list of dates that fall within the start/end date parameters and count them.

Thanks for reviewing and you help with this!

 

[jasonb75]

This should do it

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Order Date	Ready to Ship Date	Delivery Date	Delay 1 Start Date	Delay 1 End Date	Delay 2 Start Date	Delay 2 End Date	Delay 3 Start Date	Delay 3 End Date	Wait 1	Wait 2	Delay Days 1	Delay Days 2
2	02/08/2019	10/09/2019	30/10/2019	01/08/2019	05/08/2019	04/08/2019	10/08/2019	22/09/2019	23/09/2019	30	48	9	2
3	06/08/2019	09/09/2019	20/10/2019	15/10/2019	18/10/2019	17/10/2019	21/10/2019			34	36	0	5
Sheet1

Cell Formulas
Range	Formula
J2:K3	J2	=NETWORKDAYS.INTL(A2,B2-1,"0000000",IFERROR(CHOOSE({1,2,3},SEQUENCE($E2-$D2+1,,$D2),SEQUENCE($G2-$F2+1,,$F2),SEQUENCE($I2-$H2+1,,$H2)),0))
L2:M3	L2	=SUM(--(INDEX(FREQUENCY(IFERROR(CHOOSE({1,2,3},SEQUENCE($E2-$D2+1,,$D2),SEQUENCE($G2-$F2+1,,$F2),SEQUENCE($I2-$H2+1,,$H2)),0),SEQUENCE(B2-A2+1,,A2-1)),SEQUENCE(B2-A2,,2))>0))

 

[Huey72]

Thank you Jason, this works perfectly. Apologies for the slow response, in the full dataset, there were data quality issues I needed to sort through/reconcile before I could confirm the results are as expected. Many thanks for your help with this!!