Finding Overlap Times between two sets of data

[Sputnik1961]

I have been unable to get anything other than errors up to this point on this problem. All numbers are in hours

I have a set of data (3 columns by about 828 rows) that has the queue start time(D3:D830), work start time(E3:E830), and work completion time(F3:F830). These are the three columns the total number of rows will differ depending on how many parts come through in the given time period.

In a separate list, I have resource downtime [start of the downtime (W2:W10) and end of the downtime(X2:X10) ]. This is a 2 by roughly 8 (dependant on the time period).

I want to compare the two lists and determine if a part was affected by the downtime (e.g. was in the queue for a longer time because the resource was down). The end result is being able to gather metrics on downtime and its effect on total turn around time.

below is a section of the larger array and then the entire smaller array below that. My end result is I want the downtime column (right now showing #VALUE ! to show the amount time that overlaps between the Queue and completion time and anytime in the second table. So I want to first find the row that the times overlap, then determine the amount of overlap. I think the first part is my problem. I think Excel does not like that I am comparing one row to an entire array.

Thank you in advance for any help you can provide!

[TABLE="width: 484"]
<tbody>[TR]
[TD="class: xl65, width: 106"]Queue Time[/TD]
[TD="class: xl65, width: 106"]Start Time[/TD]
[TD="class: xl65, width: 147"]Completion Time[/TD]
[TD="class: xl65, width: 125"]Downtime[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 359"]
<tbody>[TR]
[TD][TABLE="width: 484"]
<tbody>[TR]
[TD]2.25[/TD]
[TD]16.00[/TD]
[TD]168.64[/TD]
[TD]#VALUE ![/TD]
[/TR]
[TR]
[TD]2.40[/TD]
[TD]176.00[/TD]
[TD]178.00[/TD]
[TD]#VALUE ![/TD]
[/TR]
[TR]
[TD]41.66[/TD]
[TD]178.00[/TD]
[TD]180.00[/TD]
[TD]#VALUE ![/TD]
[/TR]
[TR]
[TD]61.03[/TD]
[TD]180.00[/TD]
[TD]182.00[/TD]
[TD]#VALUE ![/TD]
[/TR]
[TR]
[TD]79.04[/TD]
[TD]182.00[/TD]
[TD]184.00[/TD]
[TD]#VALUE ![/TD]
[/TR]
[TR]
[TD]79.22[/TD]
[TD]184.00[/TD]
[TD]186.00[/TD]
[TD]#VALUE ![/TD]
[/TR]
[TR]
[TD]95.10[/TD]
[TD]186.00[/TD]
[TD]188.00[/TD]
[TD]#VALUE ![/TD]
[/TR]
[TR]
[TD]140.62[/TD]
[TD]188.00[/TD]
[TD]190.00[/TD]
[TD]#VALUE ![/TD]
[/TR]
[TR]
[TD]140.76[/TD]
[TD]190.00[/TD]
[TD]192.00[/TD]
[TD]#VALUE ![/TD]
[/TR]
[TR]
[TD]143.43[/TD]
[TD]192.00[/TD]
[TD]202.00[/TD]
[TD]#VALUE ![/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="width: 351"]
<tbody>[TR]
[TD]Start Time[/TD]
[TD]End Time[/TD]
[TD]TAT[/TD]
[/TR]
[TR]
[TD]17.61[/TD]
[TD]168.26[/TD]
[TD]150.64[/TD]
[/TR]
[TR]
[TD]1294.62[/TD]
[TD]1615.49[/TD]
[TD]320.87[/TD]
[/TR]
[TR]
[TD]1690.00[/TD]
[TD]1773.67[/TD]
[TD]83.67[/TD]
[/TR]
[TR]
[TD]3753.44[/TD]
[TD]3789.30[/TD]
[TD]35.86[/TD]
[/TR]
[TR]
[TD]4054.43[/TD]
[TD]4129.25[/TD]
[TD]74.82[/TD]
[/TR]
[TR]
[TD]5469.52[/TD]
[TD]5476.09[/TD]
[TD]6.56[/TD]
[/TR]
[TR]
[TD]5485.96[/TD]
[TD]5593.34[/TD]
[TD]107.38[/TD]
[/TR]
[TR]
[TD]5693.54[/TD]
[TD]5719.38[/TD]
[TD]25.83[/TD]
[/TR]
</tbody>[/TABLE]

 

[barry houdini]

Hello Sputnik1961, welcome to MrExcel, what results do you expect? I got 150.65 in row 3 using this formula

=SUM(TEXT(IF(F3>X$2:X$9,X$2:X$9,F3)-IF(D3>W$2:W$9,D3,W$2:W$9),"0.00;\0")+0)

confirmed with CTRL+SHIFT+ENTER and copied down

 

[Sputnik1961]

You are a God amongst men ... That works perfectly, Thank you!

I was trying another work around and thought I had it but after comparing to the results from what I was doing to your formula I see I was still off.

Do you mind walking me through the formula? I want to truly understand what it is doing.
The If statements part is pretty self-explanatory but I never thought to use the TEXT function and why have the +0 at the end?

Thanks again!

 

[barry houdini]

Subtracting one IF from the other gives you the correct overlap time when there is an overlap...... but for all the downtimes where there is no overlap you get negative values, so the TEXT function is just converting the negative values to zeroes, because it also turns the positive values in to text values the +0 is need to convert them back so that they can be summed.

You can do it without the TEXT function but it requires more IFs to filter out the non-overlapping downtime periods

 

[Sputnik1961]

Thanks ... that makes sense! This is definitely something I will keep in my Excel arsenal

Cheers