Subtracting Break Times From Time Sheet

[westchr5]

Good morning,

I am trying to remove break times from a time sheet, but am struggling. I am looking for a way to remove break times from someone's hours. The employees come in at various hours so not all breaks apply to each employee.


Attached is a sample worksheet. In the employee table, I manually did the calculation to show what it the output should be. I can't find out how I determine what breaks fall into a duration of time.

Any help is greatly appreciated.

Thank you

[6StringJazzer]

Put this formula in D12 and copy down. (I tried to simplify it by using an array formula but couldn't get that to work. Maybe someone else can do that.)

This assumes that a particular break applies if and only if the entire range of the break time falls within that employee's working hours. The full length of the break is always used.

=C12-B12-IF(AND(B12<A$2,B$2<C12),C$2,0)-IF(AND(B12<A$3,B$3<C12),C$3,0)-IF(AND(B12<A$4,B$4<C12),C$4,0)-IF(AND(B12<A$5,B$5<C12),C$5,0)-IF(AND(B12<A$6,B$6<C12),C$6,0)

[6StringJazzer]

Here's a better one. This goes in D12, copy down.

=C12-B12-SUMPRODUCT(--(B12<$A$2:$A$6)*--(C12>$B$2:$B$6)*$C$2:$C$6)

[westchr5]

Sorry for the delay in response. The issue I am having is if someone starts work at 8:00 PM and leaves after midnight. I was thinking of doing an "if" statement to determine if the in or out is higher. However, this created another issue of not capturing the proper break times. Any ideas on that?

Thank you for your help.

[westchr5]

BumpBump.

Thank you