R

#### Ryan A

overall process, there are 3 steps which are completed. These steps ca

be completed independently from one another, but can also (sometimes

overlap.

I am trying to determine the total time for the overall process (fro

beginning to end), without counting the duplicated times where th

steps are running parallel to one another, or times when some steps ma

be completed and are waiting for another to start.

I have outlined the basic design of the worksheet below, with times i

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) fo

each individual step, counting only work hours, excluding holidays an

weekends, using the formula below (example of step 2, which would be i

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 betwee

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 - th

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 - th

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 - th

step takes a total of 2 working hours.

Total for all three steps would be 14 working hours. What I need is

formula that will also look at the times for overlaps, gaps, and giv

me a result that would indicate took a total of non-duplicate workin

hours, which would be 9.

I would use a MIN/MAX function, factoring in the NETWORKDAYS, but ther

are sometimes when there are gaps between the steps. For instance, th

example above shows a hold time between steps 1&2, and step 3 (2 hour

of no work being done.)

This may not be possible in Excel, but I thought I would reach out t

see if anyone can provide some assistance.

Thank you