Variable Overtime Calculation

[vmcmahon]

Hello -

New regulations in our industry has led us to calculate OT a little different and I need a formula to help. Until now, overtime in our Industry was calculated at 1.5 time state Min Wage. So formula was easy/static. Now its reverted to follow the normal, whereby OT has to be paid at the hourly rate that employee is paid for their shift. Under normal conditions, this is easy to calculate, however we have one situation where we bill a static rate and pay employees a set daily rate Vs hourly rate. We need to continue to pay the daily rate for this one particular type of structure, so we need to include any OT, if earned, and keep the total within the daily rate number, if this makes sense

For simplicity the Rate is 1000/day. Staff get paid 13 Hours per day for payroll calculation purposes

Day 1 - 13 hours - No OT
Day 2 - 26 hours - No OT
Day 3 - 39 Hours - No OT
Day 4 - 52 Hours - OT
Day 5 - 65 Hours - OT
Day 6 - 78 Hours - OT
Day 7 - 91 Hours - OT

So the First 3 days - No OT. They get a flat rate of $3000

If the employee works 4 days, they get a flat rate of $4000, however now, I have to include 12 hours of OT in their pay stub.
If the employee works 5 days, they get a flat rate of $5000, however now, I have to include 25 hours of OT in their pay stub.
If the employee works 6 days, they get a flat rate of $6000, however now, I have to include 38 hours of OT in their pay stub.
If the employee works 7 days, they get a flat rate of $7000, however now, I have to include 51 hours of OT in their pay stub.

The last fly in the ointment is that the Daily rate can change depending on the contract. For example. For 4 days @ rate of $4000, through trial and error, I have figured that I need to pay 40 hours at $69 and 12 hours at 103.5 (1.5*69) to total ~ $4000

I need direction on a formula for an excel spreadsheet, where I can punch in a daily rate, and it works through each day and spit out a regular rate and OT rate which in total keeps me at the rate for the number of days. I know its possible to do this, I just cannot put my finger on how. I am thinking some type of Array or something.

I hope I am explaining this correctly, and if not please ask me to clarify.

Appreciate any help

regards
Vincent

[shg]

... I have figured that I need to pay 40 hours at $17.25 and 12 hours at 28.87 (1.5*17.25) to total ~ $4000

=40*17.25 + 12*28.87 ~ 1036, not 4000.

[vmcmahon]

=40*17.25 + 12*28.87 ~ 1036, not 4000.

Thanks SHG - TYPO. I am playing with different numbers and mixed them up. - corrected now

[shg]

Row\Col	A	B	C
1	Hours	52	B1: Input
2	Amt	$ 4,000.00	B2: Input
3	Str Rate	$ 68.97	B3: =B2 / (40 + MAX(0, B1-40)*1.5)
4	OT Rate	$ 103.45	B4: =B3*1.5

[vmcmahon]

SHG - Thank you so much. Much appreciated - works like a charm

regards
V

[shg]

You're welcome.