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.
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.