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!

 

[jasonb75]

Could you list the dates that are being counted to make up the wait figures shown in your example?

I could be missing something obvious, but I'm getting 7 and 1 for wait 1 instead of 6 and 2 (not looked at wait 2 yet).

 

[Huey72]

Thanks Jason for looking at this, I appreciate it.

For Wait 1 calculation, it is the Ready to Ship Date minus Order Date, minus any delay dates that fall within the period, excluding a delay date that occurs on the Ready to Ship Date. I'm also noticing a typo in the Delay 1 end date, it was meant to be 2019-09-03, for a single day delay, not 2019-09-30 - I'm sorry for that.

For the first record, Wait 1 is: 2019-09-10 minus 2019-09-01 = 9 days, not excluding the delay days yet. There are 3 unique delay days to remove, 2019-09-03, 2019-09-08, and 2019-09-09; the delay date of 2019-09-10 is not subtracted from wait 1 because it falls on the Ready to Ship Date, it is instead subtracted from Wait 2. So the Wait 1 time is 9 days minus 3 days = 6 days.

For the second record, Wait 1 is: 2019-09-09 minus 2019-09-06 = 3 days, not excluding the delay days yet. There is 1 unique delay day to remove, 2019-09-08; the delay date of 2019-09-09 is not subtracted from Wait 1 here because it falls on the Ready to Ship Date. So the Wait 1 time is 3 days minus 1 day = 2 days.

The nuance overall is subtracting unique overlap days, and not subtracting an overlap day when it falls on the Ready to Ship Date. Similarly, any delay day that overlaps the Delivery Date is also not counted from Wait 2, because technically, the business logic wouldn't allow for it as can't deliver if not available.

Let me know if any other questions, and again, sorry for the typo in transcribing the table.

 

[jasonb75]

I think that I have this right, the only way to know for certain would be to scrutinise the results with various combinations of overlap / no overlap.

Book2 (version 1).xlsb
	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	2019-09-01	2019-09-10	2019-09-30	2019-09-03	2019-09-03	2019-09-08	2019-09-14	2019-09-22	2019-09-23	6	13
3	2019-09-06	2019-09-09	2019-09-19	2019-09-08	2019-09-11	2019-09-18	2019-09-18			2	6
Sheet5

Cell Formulas
Range	Formula
J2:K3	J2	=NETWORKDAYS.INTL(A2,B2-1,"0000000",IFERROR(CHOOSE({1,2,3},ROW(INDIRECT($D2&":"&$E2)),ROW(INDIRECT($F2&":"&$G2)),ROW(INDIRECT($H2&":"&$I2))),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Huey72]

Thank you, I think it's close, but it looks like it works for some records, but not all records.

Here are additional sample dates to calculate Wait 1 with the formula, compared to the expected result in the next column. When using these dates, they are misaligned for some reason.

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 do you think?

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	Expected Result
2020-09-01	2020-09-06		2020-09-02	2020-09-02					5	4
2020-09-01	2020-09-06		2020-09-02	2020-09-03					5	3
2020-09-01	2020-09-06		2020-09-02	2020-09-04					5	2
2020-09-01	2020-09-06		2020-09-04	2020-09-04					5	4
2020-09-01	2020-09-06		2020-09-03	2020-09-04					5	3
2020-09-01	2020-09-06		2020-09-04	2020-09-06					5	3

 

[jasonb75]

Using the same data as above, I get the expected results.

When you enter the formula, you need to enter it into the first cell only, then array confirm it before filling down.

By trying a few other ways of entering the formula I get 4 incorrect results as well. Note that even if you have office 365 with dynamic spill arrays you will most likely still need to confirm the array the old way for it to work correctly (legacy array).

 

[Huey72]

Thanks Jason, I'm still having trouble getting that result. If I array confirm the value in the first cell and drag it down, fill it down, or highlight the empty cells and use ctrl +d to fill it down, I still get the same results. Even if I just use the first record and array confirm the formula in that row, I'm still getting 5. I am using O365. I'm not sure what I'm doing wrong. What do you think?

 

[jasonb75]

When you look at the formula bar, is the formula enclosed in curly brackets? {=NETWORKDAYS........}

 

[Huey72]

Yes it is, I've attached a shot of it.

 

[jasonb75]

What happens if you select all of the formula cells, then press F2, Ctrl Enter (no shift) to apply the formula to the range without spilling?

I don't have access to 365 to test behavior but I'm guessing that it is the dynamic arrays which are causing the issue.