Summing Time

[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

 

[Jason Morin]

Try:

=SUM(E1:E2-D12,-(E9:E11-D911),--(E1:E2<D12),--
(E9:E11<D911))

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

 

[Pete]

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

 

[Jason Morin]

To handle times than cross midnight (and to learn just
about everything you need to know about handling dates
and times in Excel), see:

http://www.cpearson.com/excel/datetime.htm#AddingTimes

This gives an example timesheet that discusses time
crossing midnight:

http://www.cpearson.com/excel/overtime.htm

HTH
Jason
Atlanta, GA