Count unique dates from multiple date ranges if with start and end dates

Huey72

New Member
Joined
Nov 6, 2019
Messages
32
Good morning everyone. I'm looking for a way to count the unique delay days from multiple date ranges, when the dates fall within the start and end dates order date to ready to ship date as period 1 or ready to ship date to delivery date as period 2. Sometimes the delay 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 the start and end dates, so I also want to exclude counting any dates that fall outside of that date range.

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!
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,611
Office Version
  1. 365
Platform
  1. Windows
This should do it
Book1
ABCDEFGHIJKLM
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 2Delay Days 1Delay Days 2
202/08/201910/09/201930/10/201901/08/201905/08/201904/08/201910/08/201922/09/201923/09/2019304892
306/08/201909/09/201920/10/201915/10/201918/10/201917/10/201921/10/2019343605
Sheet1
Cell Formulas
RangeFormula
J2:K3J2=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))
L2:M3L2=SUM(--(INDEX(FREQUENCY(IFERROR(CHOOSE({1,2,3},SEQUENCE($E2-$D2+1,,$D2),SEQUENCE($G2-$F2+1,,$F2),SEQUENCE($I2-$H2+1,,$H2)),0),SEQUENCE(B2-A2+1,,A2-1)),SEQUENCE(B2-A2,,2))>0))
 
Solution

Huey72

New Member
Joined
Nov 6, 2019
Messages
32
Thank you Jason, this works perfectly. Apologies for the slow response, in the full dataset, there were data quality issues I needed to sort through/reconcile before I could confirm the results are as expected. Many thanks for your help with this!!
 
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,152,032
Messages
5,767,739
Members
425,429
Latest member
MMMMMM

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