Compare multiple date ranges to count non-overlapping dates

Huey72

New Member
Joined
Nov 6, 2019
Messages
32
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 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
2019-09-012019-09-102019-09-302019-09-032019-09-302019-09-082019-09-142019-09-222019-09-23613
2019-09-062019-09-092019-09-192019-09-082019-09-112019-09-182019-09-1826

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
Joined
Dec 30, 2008
Messages
14,761
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 6, 2019
Messages
32
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
Joined
Dec 30, 2008
Messages
14,761
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 6, 2019
Messages
32

ADVERTISEMENT

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
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
Joined
Dec 30, 2008
Messages
14,761
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 6, 2019
Messages
32

ADVERTISEMENT

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
Joined
Dec 30, 2008
Messages
14,761
Office Version
  1. 365
Platform
  1. Windows
When you look at the formula bar, is the formula enclosed in curly brackets? {=NETWORKDAYS........}
 

Huey72

New Member
Joined
Nov 6, 2019
Messages
32
Yes it is, I've attached a shot of it.
 

Attachments

  • Formula.JPG
    Formula.JPG
    54.6 KB · Views: 18

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,761
Office Version
  1. 365
Platform
  1. Windows
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.
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top