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