Compare multiple date ranges to count non-overlapping dates

[Huey72]

Good morning everyone. I'm measuring two wait periods for a delivery cycle in days i.e., Wait 1 = the number of days between the order date and the ready to ship date, and Wait 2 = the number of days between the ready to ship date and delivery date. Within each wait period, we can have multiple date ranges where delivery is unavailable, and should be subtracted from the overall wait time. The table below outlines two sample records and the expected wait times. To note in the calculation, if there is overlap of an unavailable date with the ready to ship date, it isn't counted in Wait 1, it is counted in Wait 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
2019-09-01	2019-09-10	2019-09-30	2019-09-03	2019-09-30	2019-09-08	2019-09-14	2019-09-22	2019-09-23	6	13
2019-09-06	2019-09-09	2019-09-19	2019-09-08	2019-09-11	2019-09-18	2019-09-18			2	6

I'm looking for help with either a formula, UDF, or macros that will complete these calculations for thousands of records. Any assistance is hugely appreciated - thank you!

 

[Huey72]

I get the same result following those steps. I haven't had any luck searching for why it's not working and if related to O365 or dynamic arrays. I'll keep looking and post back if I find something. Is there an alternative approach you might recommend that isn't array driven?

 

[Fluff]

Do you have the dynamic array functions yet?

 

[Huey72]

Yes, I have O365 with dynamic arrays. I actually haven't used them yet, but I know I have them.

 

[jasonb75]

Going back to this point,

What's interesting is if I change the Delay 1 start and end dates in the first record to 2020-09-03, the formula calculates the expected result. I don't understand why that would matter in the calculation.

What happens if array confirm the formula in a single cell and fill down, then re-type the original dates?

With dates formatted as text in the first row I also get 5 as the result, possibly you have the same problem, in which case retyping the date would most likely be correcting it to a valid format.

 

[Fluff]

In that case Jason's formula should work, without using array entry

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J	K
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
2	01/09/2019	10/09/2019	30/09/2019	03/09/2019	03/09/2019	08/09/2019	14/09/2019	22/09/2019	23/09/2019	6	13
3	06/09/2019	09/09/2019	19/09/2019	08/09/2019	11/09/2019	18/09/2019	18/09/2019			2	6
4	01/09/2020	06/09/2020		02/09/2020	02/09/2020					4
5	01/09/2020	06/09/2020		02/09/2020	03/09/2020					3
6	01/09/2020	06/09/2020		02/09/2020	04/09/2020					2
7	01/09/2020	06/09/2020		04/09/2020	04/09/2020					4
8	01/09/2020	06/09/2020		03/09/2020	04/09/2020					3
9	01/09/2020	06/09/2020		04/09/2020	06/09/2020					3
Sheet4

Cell Formulas
Range	Formula
K2:K3,J2:J9	K2	=NETWORKDAYS.INTL(B2,C2-1,"0000000",IFERROR(CHOOSE({1,2,3},ROW(INDIRECT($D2&":"&$E2)),ROW(INDIRECT($F2&":"&$G2)),ROW(INDIRECT($H2&":"&$I2))),0))

 

[jasonb75]

In that case Jason's formula should work, without using array entry

Thanks for checking that, Fluff. I wasn't sure that it would spill correctly which was why I suggested legacy confirmation.

 

[Huey72]

That's it Jason, I had tried re-formatting the cells to dates, but didn't re-enter the values. When I re-enter the values it works as expected. Fluff, you are also right in that I don't have to commit the array, I can simply enter the formula and drag it down. I'll take a look with the full dataset to see if any any other calculations different than expected, but right now, this looks like it's working.

Thanks so much for your help - very much appreciated!!

 

[Fluff]

As you have the new functions you could also use
=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))
which gets rid of the volatile Indirect function

 

[Huey72]

This is fantastic, thank you both so much - saves me a ton of time to have this formula.

 

[Huey72]

I have a follow-up question to Fluff's solution above using re: 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)).

This is working perfectly for what I needed. I'm now wondering if there's a way to count the unique days returned from the choose/sequence portion of the formula, when the dates fall within the A2 and B2 dates. Sometimes the the 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 A2 (start date) & B2 (end date), so I also want to exclude counting any dates that fall outside of that date range.

Thanks for your help with this!