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– ZygDCommented Dec 9, 2021 at 3:41
Add a comment
|
1 Answer
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
