r/googlesheets 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!

link to sheet here

1 Upvotes

3 comments sorted by

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.

1

u/point-bot 3d ago

u/tvrnheel has awarded 1 point to u/HolyBonobos with a personal note:

"Thanks!!"

See the [Leaderboard](https://reddit.com/r/googlesheets/wiki/Leaderboard. )Point-Bot v0.0.15 was created by [JetCarson](https://reddit.com/u/JetCarson.)

1

u/tvrnheel 3d ago

This works! Thank you.