I am developing a tracking calculator for an overall process. Within the overall process, there are 3 steps which are completed. These steps can be completed independently from one another, but can also (sometimes) overlap.

I am trying to determine the total time for the overall process (from beginning to end), without counting the duplicated times where the steps are running parallel to one another, or times when some steps may be completed and are waiting for another to start.

I have outlined the basic design of the worksheet below, with times in mm/dd/yyyy hh:mm format.

Step 1 Start Time (B5) Complete (C5)
Step 2 Start Time (B6) Complete (C6)
Step 3 Start Time (B7) Complete (B8)

I have already accounted for the total working hours (9am to 5pm) for each individual step, counting only work hours, excluding holidays and weekends, using the formula below (example of step 2, which would be in Cell D6):

=IF(AND(INT(B6)=INT(C6),NOT(ISNA(MATCH(INT(B6),K5: K15,0)))),0,ABS(IF(INT(B6)=INT(C6),ROUND(24*(C6-B6),2),
(24*(M5-L5)*
(MAX(NETWORKDAYS(B6+1,C6-1,K5:K15),0)+
INT(24*(((C6-INT(C6))-
(B6-INT(B6)))+(M5-L5))/(24*(M5-L5))))+
MOD(ROUND(((24*(C6-INT(C6)))-24*L5)+
(24*M5-(24*(B6-INT(B6)))),2),
ROUND((24*(M5-L5)),2))))))

The problem is, I cannot find a way to account for the TOTAL time, which will calculate the overlapped time, or holding times between processes.

An example of my dilema:

If step 1 starts at 5/14/2010 4:00 pm and ends 5/17/2010 12:00 pm - the step took a total of 5 working hours.

If Step 2 starts at 5/14/2010 3:00 pm and ends 5/17/2010 2:00 pm - the step takes a total of 7 working hours.

If Step 3 starts at 5/17/2010 4:00 pm and ends 5/18/2010 10:00 am - the step takes a total of 2 working hours.

Total for all three steps would be 14 working hours. What I need is a formula that will also look at the times for overlaps, gaps, and give me a result that would indicate took a total of non-duplicate working hours, which would be 9.

I would use a MIN/MAX function, factoring in the NETWORKDAYS, but there are sometimes when there are gaps between the steps. For instance, the example above shows a hold time between steps 1&2, and step 3 (2 hours of no work being done.)

This may not be possible in Excel, but I thought I would reach out to see if anyone can provide some assistance.

Thank you,

Excel 2007 Tables
Advanced Filter
Filter weekends, holidays, working hours,
and overlapping time periods.
With optional chart and macro.
http://c0718892.cdn.cloudfiles.racks.../05_18_10.xlsx
Pdf preview (3 pgs)
http://www.mediafire.com/file/vkhmyvbnnjm/05_18_10.pdf