Summing Time

  • Thread starter Thread starter Pete
  • Start date Start date
P

Pete

I wish to calculate the time the machine has actually run.

D1 = 22:00 (Start Time) F1= Time Run
E1 = 00:15 (Finish Time)

D2 = 00:15 (Start) F2= Time Run
E2 = 02:30 (Finish)

D9 = 22:20 (Breakdown)
E9 = 22:30 (Restart)
D10 = 23:15 (Breakdown)
E10 = 23:30 (Restart)
D11 = 00:30 (Breakdown)
E11 = 02:00 (Restart)

Is there a formula that can scan a range (D9:E11) and
give me tha answer in F1 of the time run between the
Start & Finish times

any help would be appreciated

Pete
 
Try:

=SUM(E1:E2-D1:D2,-(E9:E11-D9:D11),--(E1:E2<D1:D2),--
(E9:E11<D9:D11))

Array-entered (press ctrl + shift + enter).

This gives you total run time for the 2 main runs (D1 to
E1 and D2 to E2) minus any machine downtime.

Format the formula cell as time.

HTH
Jason
Atlanta, GA
 
Jason,

Thanks. I'm not very good at explaining problems, and
whilst your solution does as stated, it's not the exact
thing I was hoping for.

E13:E20 are the stop times
F13:F20 are the start times

E5 = start of product manufacture
F5 = end of manufacture

A13:A20 is the time in minutes from F5-E5

This formula gives me what I want, but cannot handle
times that span midnight

e.g 22:30 - 00:30 in E5 & F5 respectively

Can you amend the formula to cope with that

=SUMPRODUCT(--($E$13:$E$20>=E5),--($F$13:$F$20<=F5),--
($A$13:$A$20))

I hope this makes sense

Pete
 
Back
Top