Over 40 hrs

komobu

New Member
Joined
Feb 7, 2011
Messages
34
So I am doing a work sheet that will calculate pay for employees. The employees are paid from 8 to 5 during the week, and are on call for a large part of their pay period. When they are activated, their pay starts and has to be calculated until the job is complete.

My problem is their pay will shift to overtime at time and a half when they reach 40 hours. I never know when they are going to reach it. or bu how many hours. My column "J" tracks their cumulative hours. So I might have 35 hours total on Wednesday at 5pm for one employee. The next day when I enter him for 8 hours for day shift, 5 hours will count for regular time and 3 hours will count for over time. Currently I try to now break the shift in half and make two entries. One to take him to the 40, and the other for the portion over forty. If they end on an even number, that isnt too dificult. But some times they will end the period on an odd number say 38.6 and their next task is 5.2 hours. Now it is a little more complex to break that entry down.

So I am looking for some type of formula that will track up to forty hours at one rate and over 40 hours at a different rate. Currently I use this formula "=IF(J16>=40,E17*($B$3),E17*($B$2))" J16 is the cumulative hours in the row before my entry, so if it is less than 40, use pay listed in b2 cell otherwise use pay listed in b3 cell.

Can you think of a way to write a formula where if I add 8 hours for example, based on the cumulative number in the J16 cell, it will take the first portion of the time to get to forty and compute it at B2 and the remainder of the time and compute it at cell B3?

I know it sounds confusing. So say my base rate is 10 dollars per hr in cell b2, and my OT is 15 dollars in cell b3. My cumulative hours is at 36.5 hrs in J16. I now want to add a 7.2 hr task. so it would take the first 3.5 hours and say compute that at 35 dollars for 3.5* 10. Then I would like it to compute the remaining 3.7 at 15 dollars per hour in cell b3. The OT would equal 55.5. So the pay for that cell in the line should equal 90.50. (35 straight time and 55.50 OT.)

Can you think of a way to write that formula?

Thanks for any help
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,389
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Thank you so very much. The formula "=IF(A7="","",IF(E6>=40,D7*$B$3,IF(E6+D7>40,(E7-40)*$B$3+(D7-(E7-40))*$B$2,D7*$B$2)))" is exactly what I was looking for.

Thanks Again....BTW...if you PM me your email, I'll paypal you a cup of coffee!
You are welcome - thanks for the reply, no coffee required. :biggrin:
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,720
Members
414,401
Latest member
grenona2020

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top