total hours worked calculation with breaks

[jsgjr]

Hi everyone,

I am trying to figure out a way to calculate the total hours scheduled with break. I know there is an "IF" function, but I do not know how to add it to the current formula.

This is the current formula which calculates the time "shift end-shift start" in column "P". But if someone is working more than 5 hours, I would like the formula to subtract 30 mins for break. Is this possible?
=(C9-B9)*24+(E9-D9)*24+(G9-F9)*24+(I9-H9)*24+(K9-J9)*24+(M9-L9)*24+(O9-N9)*24

thank you in advance for your help!

 

[awoohaw]

for each shift can you do something like IF(EndTime - BegTime > 5/24, EndTime - BegTime - 1/48, EndTime - BegTime),
There are probably some more efficient ways to do that, but this is straightforward. The only concern is if the BegTime is in next day, then you'd have to do an If to add 1 day before the subtraction.