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!
 
Could you post a sample with a couple of examples of each scenario where the formula is not giving the expected result, along with the results that you are expecting please.

I haven't read over the thread in detail but at a glance I believe that the formula should return the correct results with overlapping dates but possibly not with dates outside of the specified range.
 
Upvote 0

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Thanks for looking at this. The formula works as expected for the first count I need, it does ignore dates outside the range and removes duplicates and gives me the expected wait 1 and wait 2 days. I'm now adding two more counts, to count the unique delay days. Here are two examples with expected results for delay days 1 and delay days 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 2Delay Days 1Delay Days 2
2019-08-022019-09-102019-10-302019-08-012019-08-052019-08-042019-08-102019-09-222019-09-23304883
2019-08-062019-09-092019-10-202019-10-152019-10-182019-10-172019-10-21343605


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!
 
Upvote 0
As this is a significantly different question to your op, you need to start a new thread. Thanks.
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

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
Back
Top