Prev: how do I attach a jpeg to a cell
Next: Excel Doc
From: Ryan A on 17 May 2010 14:18

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,




--
Ryan A
From: Herbert Seidenberg on 19 May 2010 13:32
Excel 2007 Tables
Advanced Filter
Filter weekends, holidays, working hours,
and overlapping time periods.
With optional chart and macro.
http://c0718892.cdn.cloudfiles.rackspacecloud.com/05_18_10.xlsx
Pdf preview (3 pgs)
http://www.mediafire.com/file/vkhmyvbnnjm/05_18_10.pdf

 | 
Pages: 1
Prev: how do I attach a jpeg to a cell
Next: Excel Doc