Lookup and Calculate Overlap of a Date Range to a Second Date Range

[lac0403]

I have seen the posts on using Lookup to check if a date falls between a range of dates, but I need to compound that now and check the range of dates against a range of dates. I have start & stop dates for steps in a process, but if the days include holidays, I need to adjust the total time used by the number of days of the holidays.

Sheet1: Column Q = Step Start Dates
Sheet1: Column R = Step End Dates
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Q[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]Step Start[/TD]
[TD]Step End[/TD]
[/TR]
[TR]
[TD]12/20/2017[/TD]
[TD]12/28/17[/TD]
[/TR]
[TR]
[TD]12/28/17[/TD]
[TD]1/5/2018[/TD]
[/TR]
</tbody>[/TABLE]


Sheet 2: Column H = Holiday Start Date
Sheet 2: Column I = Holiday End Date
Sheet 2: Column J = Number of Days to Adjust Total Time

[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]Start[/TD]
[TD]End[/TD]
[TD]Max Adj[/TD]
[/TR]
[TR]
[TD]11/24/2017[/TD]
[TD]11/25/2017[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]12/26/17[/TD]
[TD]12/30/2017[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]1/1/2018[/TD]
[TD]1/1/2018[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]



I need to check the range of dates from Sheet1: Q to R, to see if it contains the dates in the holiday ranges of Sheet 2: Column H to I.

And then it gets even more complicated: If they overlap by only a portion, then I only adjust part of the time.

For example:

With the following data:

[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]12/20/2017[/TD]
[TD]12/28/2017[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]1/1/2018[/TD]
[TD]1/3/2018[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


Column S: Should return 3 for the three days of overlap: 12/26/17, 12/27/17, 12/28/17. And return 1 for the 1/1/2018 row.

Any guidance and help is appreciated.

Using Excel 2016. (VBA is an Option, I have basic VBA skills.)

 

[Marcelo Branco]

You need an array formula - something like this

Array formula in S2 copied down
=SUM(IF(1-((H$2:H$4>R2)+(Q2>I$2:I$4)),1+IF(I$2:I$4>R2,R2,I$2:I$4)-IF(H$2:H$4>Q2,H$2:H$4,Q2)))
confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

 

[lac0403]

Sorry for the so late reply. Got it working! Thanks Marcelo.