Forum Discussion
Excel formula to take time off from a time range
Understand the logic
You have time ranges and corresponding break deductions:
Hours Worked (C2) | Break Deduction |
4:00 – 5:59 | 15 minutes |
6:00 – 7:59 | 30 minutes |
8:00 – 8:59 | 60 minutes |
9:00 – 11:59 | 90 minutes |
You want D2 = C2 - break.
Correct Excel Formula:
=IF(AND(C2>=TIME(4,0,0),C2<=TIME(5,59,0)), C2-TIME(0,15,0),
IF(AND(C2>=TIME(6,0,0),C2<=TIME(7,59,0)), C2-TIME(0,30,0),
IF(AND(C2>=TIME(8,0,0),C2<=TIME(8,59,0)), C2-TIME(1,0,0),
IF(AND(C2>=TIME(9,0,0),C2<=TIME(11,59,0)), C2-TIME(1,30,0),
C2))))
Make sure C2 is in time format (e.g., [h]:mm if it can exceed 24 hours).
TIME(hours, minutes, seconds) works the same on Mac.
My answers are voluntary and without guarantee!
Hope this will help you.
alternatively:
=C2-TIME(0,15,0)*( (C2>=TIME(4,0,0) ) + (C2>=TIME(6,0,0) ) + 2*(C2>=TIME(8,0,0) ) + 2*(C2>=TIME(9,0,0) ) )basically add increments of 15min based on if >4hr add 1, >6hr add another 1, >8hr add 2 more, and >9hr add another 2.
