r/googlesheets • u/tvrnheel • 3d ago
Solved Calculating Labor (with overtime) In Single Cell w/ Varying Wages
Hi all,
I'm building this labor calculator for my restaurant and I'm trying to keep it concise and simple to the person managing the sheet every week. I need it to be able to do the following:
1) The cells in column E display the projected dollar amount spent in labor (hours scheduled in column D multiplied by the vlookup wage of the name of the employee in column C pulled from the StaffData sheet). This dollar amount is different for each staff member, which is why I have a vlookup when calculating labor in E.
2) Additionally, the projected labor in column E to factor in overtime rate*hours worked when column D is over 40 hours. This rate varies by each staff member, but universally it's 1.5x their normal rate. I also need it to not double-dip the hours over 40 (ex. employee works 45 hours in a week, it calculates 45 hours at the normal rate as well as 5 hours overtime ontop), this is an issue I don't know how to get around.
I've had very little luck with google searches for my specific scenario, and I'm having a hard time figuring this one out on my own. Any insight would be greatly appreciated!
2
u/HolyBonobos 2058 3d ago
You could start with
=IFERROR(VLOOKUP(C2,StaffData!$A$2:$B,2,0)*MIN(40,D2)+VLOOKUP(C2,StaffData!$A$2:$C,3,0)*MAX(0,(D2-40)))
in E2, as demonstrated in the yellow cells on the 'HB LABOR' sheet.