Overtime Calculations

B

bgcooker

I have a spreadsheet tracking the time a salaried person starts, when they
leave for lunch, when they return from lunch, and when they leave at the end
of the workday. The spreadsheet totals the hours and deducts the time taken
for lunch with no problem. I want to create a formula that will use the
total net hours worked for the week and then calculate the net hourly wage if
the person was hourly. I want to ensure that wages paid are accurately
compensating the hours worked for a salaried person. I currently have a
separate formula that performs that calculation, but it requires me to deduct
40 hours, convert the remaining time into a decimal format, and enter that
into the formula. The formula I am using for that calculation is
=1000/(40+((14.2)*1.5)). The 1000 is using $1000 as an example for the
person's weekly salary, and the 14.2 are the hours worked over 40 hours,
which would be multiplied times 1.5 if the person was hourly. I use a formula
=SUM(G12:G16) that sums up the normal five-day workweek. It is formatted
using [h]:mm. I'm having trouble creating a formula that uses the resulting
total hours from this formula in the previous formula. Sorry for all the
detail, but this one has me stumped.
 
L

Luke M

Just need to multiply your SUM by 24 to get a unit base of hrs.
=A2/(MIN(40,G17*24)+1.5*MAX(0,G17*24-40))

This formula has weekly salary in A2, and your SUM in G17. Now you don't
have to convert to decimals and input that into formula.
 
B

bgcooker

Thanks - this worked great.

Luke M said:
Just need to multiply your SUM by 24 to get a unit base of hrs.
=A2/(MIN(40,G17*24)+1.5*MAX(0,G17*24-40))

This formula has weekly salary in A2, and your SUM in G17. Now you don't
have to convert to decimals and input that into formula.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


bgcooker said:
I have a spreadsheet tracking the time a salaried person starts, when they
leave for lunch, when they return from lunch, and when they leave at the end
of the workday. The spreadsheet totals the hours and deducts the time taken
for lunch with no problem. I want to create a formula that will use the
total net hours worked for the week and then calculate the net hourly wage if
the person was hourly. I want to ensure that wages paid are accurately
compensating the hours worked for a salaried person. I currently have a
separate formula that performs that calculation, but it requires me to deduct
40 hours, convert the remaining time into a decimal format, and enter that
into the formula. The formula I am using for that calculation is
=1000/(40+((14.2)*1.5)). The 1000 is using $1000 as an example for the
person's weekly salary, and the 14.2 are the hours worked over 40 hours,
which would be multiplied times 1.5 if the person was hourly. I use a formula
=SUM(G12:G16) that sums up the normal five-day workweek. It is formatted
using [h]:mm. I'm having trouble creating a formula that uses the resulting
total hours from this formula in the previous formula. Sorry for all the
detail, but this one has me stumped.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Work hours with overtime 2
Simple Time sheet, calculate OT 2
Work Budget Salary vs Real Time Salary 0
Timesheet - Formula 4
Excel Formula 2
Calculating Time 6
Complex Timesheet 10
TIME SHEETS 1

Top