Count Hours between Start Time & End Time but exclude Break Hours

Dear Sifu

Help! I am trying to calculate total working hours after deducting break hours that one is entitled to.

In below scenario, the person worked from 8:30 AM to 6:15 PM.

He is entitled to lunch break because he has worked for 4.5 hours (8:30 AM to 1:00 PM) before the lunch break.

He is also entitled to afternoon tea break because he has worked for 2 hours (2:00 PM to 4:00 PM) before the afternoon tea break.

But he is not entitled to morning break because he has only worked for 1.5 hours (8:30 AM to 10:00 AM).

In cell C6, I have this formula: "=SUMPRODUCT((C12:C14>=C3)*(C12:C14<=C4)*(D12:D14)/24)*24".

How do I factor in the qualification for the break hours?

Thanks in advance!
Joseph

Count Hours.png

Would it work to make a new column next to Min. Start Time to qualify which is the time he has qualified for? So: =IF(F12<C3,D12,0)
That way if the start time is before the qualify time, you get the duration. Then in C6 you can add up just the breaks that have been qualified for?

Thanks for your reply ocannon1... we have thousands of workers. The spreadsheet format in use is unlike the above. It will be difficult to incorporate your suggestion.

Maybe

=SUMPRODUCT((F12:F14>=C3)*(C12:C14<=C4)*D12:D14)

Oh ya... I could use back the same formula for the break hours qualification... but column F was created to showcase how the qualification works only.
Supposed I don't have the column F, how could I adjust the formula?

There is another problem, if the clock out time (End Time) is during the break hours or on the Start Time of the break hours, the total break hours would be incorrect.

Take example below, the person clocked out at 4:15pm, so he only utilised 15 minutes for his afternoon tea break, not the full 30 minutes.

How do I adjust that?

Count Hours 2.png

Thanks, Root... both formulas worked very well!! However, I am looking for formulas that do not look at each line of breaks (we have more breaks than the 3 stated). it would be okay to retain column F.

Try this -- it takes in break rages (not individual lines):

=SUMPRODUCT((C3<=C12:C14-E12:E14/24)*(C4>=C12:C14)*(D12:D14*(D12:D14<(C4-C12:C14)*24)+(C4-C12:C14)*24*(D12:D14>=(C4-C12:C14)*24)))

Dear Root

Formula #3 does not give the same results as Formula #1 and Formula #2...

Count Hours 3.png

Originally Posted by josephteh

Dear Root

Formula #3 does not give the same results as Formula #1 and Formula #2...

It's because you did not select the correct ranges for the new layout.

The formula takes in the following ranges: break start time, break duration, and min hours worked.

So, for the new layout, the formula should be:

=SUMPRODUCT((C3<=$C$12:$C$14-$F$12:$F$14/24)*(C4>=$C$12:$C$14)*($E$12:$E$14*($E$12:$E$14<(C4-$C$12:$C$14)*24)+(C4-$C$12:$C$14)*24*($E$12:$E$14>=(C4-$C$12:$C$14)*24)))

Oh ya.. copied wrongly.. the formula works perfect! Thanks!