Over 40 hrs

komobu

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

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

JoeMo

MrExcel MVP
Like this? Copy K16 down.
Excel Workbook
JK
15Cum HrsPay
1643.7455.50
1738.5385.00
Comm_Sept 2019 Brazil Summary

komobu

New Member
Joe...Thanks for the help. I still cant quite get it.

Your formula would give me the pay for the total hours in the week and I am trying to get it just for that task / day. So the number in this case should end up at 77.5 for your example. You would have a total task of 5.2 hours. the first 1.5 hours would be calculated at cell B2 (16.50) and the rest of the hours, in this case 3.7 would be calculated at cell B3 (61.05). The total number would equal 77.50 for this.

I dont know how to insert a table in this, but here is a copy and paste to give you an idea. If you look at row 22 and 23, you can see where I broke out the 8 hour day into two sections so I could figure out the daily pay correctly. I would rather excel do it for me with one entry if possible...

Thanks Again

 Column A Column B Column C Column D Column E Column F Column G Column H Column I Column J 1 Employee Smith 2 Hourly Rate \$11.00 \$440.00 Sub Total Total 3 OT Rate \$16.50 \$743.88 \$1,183.88 85.08 \$1,183.88 \$233.71 \$1,417.58 4 5 Date Start Time End Time Length Total Hrs Time Pay Commision 1/3 Cmn Rate Generated Total Hours 6 0 7 10/1/2019 8:00 16:00 8:00 8.00 88.00 0.00 0.00 0 8.00 8 10/1/2019 18:20 19:00 0:40 0.67 7.33 7.67 15.00 45 8.67 9 10/1/2019 19:10 20:20 1:10 1.17 12.83 8.83 21.67 65 9.83 10 10/1/2019 23:00 0:30 1:30 1.50 16.50 23.50 40.00 120 11.33 11 10/1/2019 3:00 3:45 0:45 0.75 8.25 11.75 20.00 60 12.08 12 10/2/2019 8:00 16:00 8:00 8.00 88.00 0.00 0.00 0 20.08 13 10/2/2019 18:20 20:20 2:00 2.00 22.00 0.00 15.00 45 22.08 14 10/2/2019 19:10 20:20 1:10 1.17 12.83 8.83 21.67 65 23.25 15 10/2/2019 23:00 0:30 1:30 1.50 16.50 23.50 40.00 120 24.75 16 10/3/2019 3:00 3:45 0:45 0.75 8.25 11.75 20.00 60 25.50 17 10/3/2019 8:00 16:00 8:00 8.00 88.00 0.00 0.00 0 33.50 18 10/3/2019 18:20 19:00 0:40 0.67 7.33 7.67 15.00 45 34.17 19 10/3/2019 19:10 20:20 1:10 1.17 12.83 8.83 21.67 65 35.33 20 10/3/2019 23:00 0:30 1:30 1.50 16.50 23.50 40.00 120 36.83 21 10/4/2019 3:00 3:45 0:45 0.75 8.25 11.75 20.00 60 37.58 22 10/4/2019 8:00 10:25 2:25 2.42 26.58 0.00 0.00 0 40.00 23 10/4/2019 10:25 16:00 5:35 5.58 92.13 0.00 0.00 0 45.58 24 10/4/2019 18:20 19:00 0:40 0.67 11.00 4.00 15.00 45 46.25 25 10/4/2019 19:10 20:20 1:10 1.17 19.25 2.42 21.67 65 47.42 26 10/4/2019 23:00 0:30 1:30 1.50 24.75 15.25 40.00 120 48.92 27 10/5/2019 3:00 3:45 0:45 0.75 12.38 7.63 20.00 60 49.67 28 10/5/2019 8:00 16:00 8:00 8.00 132.00 0.00 0.00 0 57.67

<tbody>
</tbody>

<tbody>
</tbody>

JoeMo

MrExcel MVP
Do I understand correctly:
1. employees are paid daily?
2. an employee may work more than 8 hours on any given day?
3. an employee may log In and Out more than once on any given day?
For the data you show for 10/4/2019, the employee may actually have logged In & Out fewer times than you show (you show 6 times) because you added one of more rows after the employee reached a total of 40 hours?

komobu

New Member
Yes...I am trying to track everything daily. The employees are tow truck drivers. They work an 8 hour day 5 days a week. They are then on call every other work day, and every other weekend. Then sick days or appts are thrown in. So what I want to do is calculate their pay with running totals for every task. I am calling a task an 8 hour period during the week, or a tow at night or on the weekend. If they tow at night or on the weekend, their pay would start when they get dispatched and end when they complete.

I am wanting to track everything by the day so I can show how pay is calculated if I get any questions. Also, since I will never know how many tows will happen at night, I never know when they will reach the 40 hrs and have to start paying overtime. So it seems to be better to track their pay by every day or task.

JoeMo

MrExcel MVP
I'm still a bit fuzzy about this, but here's a first pass using the data you posted you can look at. The formulas shown all need to be copied down to cover all the data. Col D should be formatted as a Number. I have no idea what cols G:I are all about so I have ignored them. What I've tried to do is show you what the employee is entitled to at the end of each day. There is no need to breakout the employee entries for any day where the employee has met or exceeded 40 hours.
Excel Workbook
ABCDEFGHIJK
1EmployeeSmith
2Hourly Rate\$11.00
3OT Rate\$16.50
4
5DateStart TimeEnd TimeHrsCum HrsTime PayCommision1/3 Cmn RateGeneratedPay By DayPay Date
6
710/1/20198:0016:008.008.00\$88.00000
810/1/201918:2019:000.678.67\$7.337.671545
910/1/201919:1020:201.179.83\$12.838.8321.6765
1010/1/201923:000:301.5011.33\$16.5023.540120
1110/1/20193:003:450.7512.08\$8.2511.752060\$132.92For 10/1/2019
1210/2/20198:0016:008.0020.08\$88.00000
1310/2/201918:2020:202.0022.08\$22.0001545
1410/2/201919:1020:201.1723.25\$12.838.8321.6765
1510/2/201923:000:301.5024.75\$16.5023.540120\$139.33For 10/2/2019
1610/3/20193:003:450.7525.50\$8.2511.752060
1710/3/20198:0016:008.0033.50\$88.00000
1810/3/201918:2019:000.6734.17\$7.337.671545
1910/3/201919:1020:201.1735.33\$12.838.8321.6765
2010/3/201923:000:301.5036.83\$16.5023.540120\$132.92For 10/3/2019
2110/4/20193:003:450.7537.58\$8.2511.752060
2210/4/20198:0010:252.4240.00\$26.58000
2310/4/201910:2516:005.5845.58\$92.13000
2410/4/201918:2019:000.6746.25\$11.0041545
2510/4/201919:1020:201.1747.42\$19.252.4221.6765
2610/4/201923:000:301.5048.92\$24.7515.2540120\$181.96For 10/4/2019
2710/5/20193:003:450.7549.67\$12.387.632060
2810/5/20198:0016:008.0057.67\$132.00000\$144.38For 10/5/2019
Sheet6 (2)

komobu

New Member
Thanks Joe, I will look at it now. As an aside, the golumns g through I are for tower commission. The tower will get 1/3 of what ever the truck generates for the particular tow. The "Time" plus the "Commission" will equal the 1/3rd of the truck bill. So if the tow truck earns 180 dollars for a tow, the driver will get 1/3, or 60. If he worked 1 hour of sT, that would be 11.00 and the commission would be 49.00 dollars. If the tow took him 2 hrs, than time would be 22 dollars. and the commission would be 38. Either way, it earned him 60 dollars or 1/3 of what the truck generated.

komobu

New Member
Hi again Joe;

I still cant get it to work. Please look at lines/rows 22 and 23. I have them spit so that the first part will take me to 40 hrs on straight time, and the second period will be OT. If I enter the eight hour period as one entry, which is what I am trying to do, it will give the whole 8 hours as overtime instead of just the last 5.5 of the period.

So say my cumulative hours is 36. My next shift is 8 hours. 4 of those hours should be straight time equaling 44.00, and 4 of those hours should be OT equaling 66.00. Together that task should equal 110.00. The formula is listing the whole period at overtime of 132.00

It may be easier for me to split the pay period once I exceed 40 hours.

JoeMo

MrExcel MVP
Hi again Joe;

I still cant get it to work. Please look at lines/rows 22 and 23. I have them spit so that the first part will take me to 40 hrs on straight time, and the second period will be OT. If I enter the eight hour period as one entry, which is what I am trying to do, it will give the whole 8 hours as overtime instead of just the last 5.5 of the period.

So say my cumulative hours is 36. My next shift is 8 hours. 4 of those hours should be straight time equaling 44.00, and 4 of those hours should be OT equaling 66.00. Together that task should equal 110.00. The formula is listing the whole period at overtime of 132.00

It may be easier for me to split the pay period once I exceed 40 hours.
The formula in F7 (copied down) was incorrect. Revised formula is below. In the table note that line 22 now has the full 8 hours, the old line 23 has been removed. The part of the 8 hours that brings the cum to 40 is paid at the regular rate, the balance at the OT rate. Thanks for your explanation of cols G:I, I'll leave to you to deal with them.
Excel Workbook
ABCDEFGHIJK
1EmployeeSmith
2Hourly Rate\$11.00
3OT Rate\$16.50
4
5DateStart TimeEnd TimeHrsCum HrsTime PayCommision1/3 Cmn RateGeneratedPay By DayPay Date
6
710/1/20198:0016:008.008.00\$88.00000
810/1/201918:2019:000.678.67\$7.337.671545
910/1/201919:1020:201.179.83\$12.838.8321.6765
1010/1/201923:000:301.5011.33\$16.5023.540120
1110/1/20193:003:450.7512.08\$8.2511.752060\$132.92For 10/1/2019
1210/2/20198:0016:008.0020.08\$88.00000
1310/2/201918:2020:202.0022.08\$22.0001545
1410/2/201919:1020:201.1723.25\$12.838.8321.6765
1510/2/201923:000:301.5024.75\$16.5023.540120\$139.33For 10/2/2019
1610/3/20193:003:450.7525.50\$8.2511.752060
1710/3/20198:0016:008.0033.50\$88.00000
1810/3/201918:2019:000.6734.17\$7.337.671545
1910/3/201919:1020:201.1735.33\$12.838.8321.6765
2010/3/201923:000:301.5036.83\$16.5023.540120\$132.92For 10/3/2019
2110/4/20193:003:450.7537.58\$8.2511.752060
2210/4/20198:0010:258.0045.58\$118.71000
2310/4/201918:2019:000.6746.25\$11.0041545
2410/4/201919:1020:201.1747.42\$19.252.4221.6765
2510/4/201923:000:301.5048.92\$24.7515.2540120\$181.96For 10/4/2019
2610/5/20193:003:450.7549.67\$12.387.632060
2710/5/20198:0016:008.0057.67\$132.00000\$144.38For 10/5/2019
Sheet6 (2)

komobu

New Member
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!

Last edited: