Overtime Calculations

[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.

 

[Eduardo]

Hi,
take a look to CPearson web

http://www.cpearson.com/excel/overtime.htm

If this was helpful please say yes, thanks

 

[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.

 

[bgcooker]

Thanks - this worked great.

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!*

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.

 

[Shane Devenshire]

Hi,

Here is one solution:

=A2/(24*G17+MAX(G17-5/3,0)*12)