0

I'm not very savvy with Google Sheets/Excel so I have been fumbling my way though attempting this. I'm trying to calculate amount of overtime pay for a given time worked. For instance: From hours 0-12, no OT pay. Hours 12:00-14:00 - a certain amount per hour. And finally from 14:00-16:00, a different amount per hour. I would like the cell to display the total amount of OT pay for the day. So if I work a 14:33 day, I'd like to know the total OT pay. I have the hourly rates for each segment in a reference cell, but for the life of me I can't come up with a formula that calculates what I need in a single cell. Any help is appreciated.

1
  • Sharing a copy of your example Google Sheets file would be hwlpful
    – ZygD
    Dec 9, 2021 at 3:41

1 Answer 1

1

Transform your HH:MM to hours worked (HH+MM/60) and store it on (say) cell B1. If you have the hourly rates in cells A1 (regular hours), A2 (first 2 OT hours) to A3 (subsequent OT hours), then using

=min(b1,12)*a1+min(max(0,b1-12);2)*a2+min(max(b1-14;0);2)*a3

should work

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.