Tiered hourly pay calculator

**m5rcc** · 08-05-2012, 06:14 PM

I am trying to work out how much I should get paid in gross per day.

In the attached spreadsheet, the "Pay rates" tab shows how much I should get paid per hour, so if I did six hours it would simply be 6 x £10, if I did 13, it would (8 x 10) + (2 x 12) + (2 x 14) + (1 x 16).

How can I portray that correct gross pay on column E of "sheet 2"?

Any help would be greatly appreciated.

**shg** · 08-05-2012, 06:21 PM

Please Login or Register  to view this content.

In E2 and copied down,

=10 * MAX(0, MIN(8, (C2-B2)*24)) + 12 * MAX(0, ((C2-B2)*24 - 8))

**m5rcc** · 08-05-2012, 06:27 PM

Originally Posted by shg

Please Login or Register  to view this content.

In E2 and copied down,

=10 * MAX(0, MIN(8, (C2-B2)*24)) + 12 * MAX(0, ((C2-B2)*24 - 8))

Thanks - does your formula omit days that I would do more than 12 hrs?

So would I need to do....

=10 * MAX(0, MIN(8, (C2-B2)*24)) + 12 * MAX(0, ((C2-B2)*24 - 8)) + 14 * MAX(0, ((C2-B2)*24 - 10)) ??

**oeldere** · 08-05-2012, 06:53 PM

Or this one (see the example).

I think i made an mistake.

Here the example with the good result.

Please Login or Register  to view this content.

**Ace_XL** · 08-06-2012, 02:56 AM

Maybe..

=SUMPRODUCT(--((C2-B2)*24>{0;8;10;12}),(C2-B2)*24-{0;8;10;12},{10;2;2;2})

**bojef** · 11-24-2023, 08:59 PM

Originally Posted by m5rcc

I am trying to work out how much I should get paid in gross per day.

In the attached spreadsheet, the "Pay rates" tab shows how much I should get paid per hour Finance, so if I did six hours it would simply be 6 x £10, if I did 13, it would (8 x 10) + (2 x 12) + (2 x 14) + (1 x 16).

How can I portray that correct gross pay on column E of "sheet 2"?

Any help would be greatly appreciated.

This might just be a maths question rather than an employment question, but I'm no good at either :o I get paid monthly, same amount each month whether it's got 28,29,30 or 31 days. My contracted times are 37.5 hours a week. I've been offered overtime at 1.75 times hourly rate - but I don't know what that is! I did think of just dividing my monthly wage by 4 (weeks) then 37.5 (hours) until I realised that there are more than 48 weeks in a year

If we assume that i earn £50k a year (I so so don't!) can someone tell me what that is per hour? Or what I need to do to calculate it myself? Thank you

**JohnTopley** · 11-25-2023, 02:29 AM

If you are paid an annual salary: divide by 52 [weeks] than by 37,5 to get your hourly pay rate.

So $50K = $961 per week= (approximately) $25 per hour