Overtime calculations with 3 rates of pay

lostinexcelformulas

New Member
Joined
Apr 10, 2009
Messages
15
I'm struggling with a formula to calculate 3 different rates of pay as part of a timesheet. That being rates of *1, *1.33 & *1.5.
If the employee works normal 8hrs then single rate *1 applies. After those 8 hours working then for a period of 2 hours *1.33 rate applies. After effectively the 8 hours + 2 hours then rate *1.5 applies.

I have one cell displaying total hours, a cell showing hours at *1.33 and a final cell showing anything >2:15hrs. How can go about this problem??
I have tried the limited knowledge that I have to complete this problem but end up going round in circles.
Any ideas. I'm still using Ex'97. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>

<o:p> </o:p>
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
If you have hours (as a decimal, not time) in A1 and rate in B1 then this formula will give the total pay

=(1.5*A1-MIN(A1,10)*(1.5-1.33)-MIN(A1,8)*(1.33-1))*B1
 
Upvote 0
You seem to have several different versions of this question going, 2 here and one at Excelforum. Rather than posting the same question multiple times why not give a little more information in one single thread?

You have total hours for the shift.....in what format, if you have 11½ hours is that shown as 11.5 or 11:30?

Do you want that split into the hours for each category and then the amount for each, or something else?
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

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