Automatic Net Working Time Calculation Including Breaks

Dear All,

I am working for a mass production company and we are keeping production logs in excel. I have searched deeply but did not find any answer to my question in excel. Now the question;

Our workers in two shifts are working in different machines for producing different products. They need to log in for each machine and each product. They have also the breaks below;

10:00-10:15 Tea Break
12:45-13:15 Lunch
15:00-15:15 Tea Break
19:00-19:30 Dinner
22:00-22:30 Tea Break

Their shifts are 08:30-16:30 & 16:30-00:30. The example table is given below;

Product Worker Start Finish Quantity Net Working Time (min)
A101 Robert 08:30 10:30 50
A102 Robert 10:30 14:00 40
A103 Robert 14:00 16:30 70
B101 John 16:30 20:00 60
B102 John 20:00 00:30 40

Is there a way to calculate the Net Working Times considering the tea and meal breaks without VB? For example for Robert producing A101, the equation should yield 105 minutes since Robert has a tea break between 10:00-10:15.

I'm also open to suggestions with VB of course=)

The net working time is important because we are calculating the Quantity/Shift KPIs. I simplified the problem a bit since I could not solve this part.

Thank you for your kind help!

Hi,

try this:

Excel 2013 32 bit

	A	B	C	D	E	F	G
1	Shifts
2	Shift 1	08:30:00	16:30:00
3	Shift 2	16:30:00	00:30:00
4
5	Breaks
6	Shift 1	Tea Break	10:00	10:15	=D6-C6
7	Shift 1	Lunch	12:45	13:15	=D7-C7
8	Shift 1	Tea Break	15:00	15:15	=D8-C8
9	Shift 2	Dinner	19:00	19:30	=D9-C9
10	Shift 2	Tea Break	22:00	22:30	=D10-C10
11
12
13
14
15	Product	Worker	Start	Finish	Quantity	Net Working Time (min)
16	A101	Robert	08:30	10:30	50	=(D16+(D16<C16))-C16-SUMPRODUCT((C16<=$C$6:$C$10)*((D16+(D16<C16))>=$D$6:$D$10),$E$6:$E$10)
17	A102	Robert	10:30	14:00	40	=(D17+(D17<C17))-C17-SUMPRODUCT((C17<=$C$6:$C$10)*((D17+(D17<C17))>=$D$6:$D$10),$E$6:$E$10)
18	A103	Robert	14:00	16:30	70	=(D18+(D18<C18))-C18-SUMPRODUCT((C18<=$C$6:$C$10)*((D18+(D18<C18))>=$D$6:$D$10),$E$6:$E$10)
19	B101	John	16:30	20:00	60	=(D19+(D19<C19))-C19-SUMPRODUCT((C19<=$C$6:$C$10)*((D19+(D19<C19))>=$D$6:$D$10),$E$6:$E$10)
20	B102	John	20:00	00:30	40	=(D20+(D20<C20))-C20-SUMPRODUCT((C20<=$C$6:$C$10)*((D20+(D20<C20))>=$D$6:$D$10),$E$6:$E$10)

Sheet: Sheet11

Thank you for your help, you are a life saver! Also i have learned about sumproduct with arrays, which i do not before

Originally Posted by eksert

Thank you for your help, you are a life saver! Also i have learned about sumproduct with arrays, which i do not before

Glad I could help. Please remember to mark this as solved and if you pleased with my help click "add reputation"