R
Ryan A
I am developing a tracking calculator for an overall process. Within th
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
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