# Compare multiple date ranges to count non-overlapping dates

#### Huey72

##### New Member
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!

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### jasonb75

##### Well-known Member
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

##### New Member
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

##### Well-known Member
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
ABCDEFGHIJK
1Order DateReady to Ship DateDelivery DateDelay 1 Start DateDelay 1 End DateDelay 2 Start DateDelay 2 End DateDelay 3 Start DateDelay 3 End DateWait 1Wait 2
22019-09-012019-09-102019-09-302019-09-032019-09-032019-09-082019-09-142019-09-222019-09-23613
32019-09-062019-09-092019-09-192019-09-082019-09-112019-09-182019-09-1826
Sheet5
Cell Formulas
RangeFormula
J2:K3J2=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

##### New Member

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
 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
54
 2020-09-01
 2020-09-06
 2020-09-02
 2020-09-03
53
 2020-09-01
 2020-09-06
 2020-09-02
 2020-09-04
52
 2020-09-01
 2020-09-06
2020-09-042020-09-0454
 2020-09-01
 2020-09-06
2020-09-032020-09-0453
 2020-09-01
 2020-09-06
2020-09-042020-09-0653

#### jasonb75

##### Well-known Member
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

##### New Member

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

##### Well-known Member
When you look at the formula bar, is the formula enclosed in curly brackets? {=NETWORKDAYS........}

#### Huey72

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

#### Attachments

• Formula.JPG
54.6 KB · Views: 18

#### jasonb75

##### Well-known Member
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.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,046
Messages
5,835,106
Members
430,343
Latest member
Sailingexcel

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back