Excel calculating penalty payment

Question

If all hours worked prior to 12:00pm on a Saturday are paid at normal time (1.0). Hours worked after 12pm on the same shift are paid at penalty (1.5)

So if a staff member worked an 8 hour shift on a Saturday starting at 9am, it would pay 5 hours of the 8 hour shift with an additional 50% penalty.

• A1 = Shift duration

• A2 = Shift start

• A3 = Break time (calculated as 1 hour only if shift duration is greater than 5 hours – my last question kindly already solved!)

• A4 = Shift Finish (A2 + A1)+A3

I need to calculate the payment in this instance. Base rate payment is A1 * hourly rate – that’s straight forward - but calculating the penalty rate is where I’ve lost it!

I need a formula to calculate all hours worked after 12:00pm and then multiply by 0.5 to calculate the value of the penalty payment.

Answer 1

I think this is what you mean (I used A5 for the hourly rate):

=IF(A2>(12/24),A1*1.5*A5,IF(A2+(A1/24)<=12/24,A1*A5,(((12/24-A2)*24)+((A1-((12/24-A2)*24))*1.5))*A5))

PS Don't you also need a date cell somewhere to check for Saturday or not? =IF(WEEKDAY(day_cell)=7,Saturday-formula,RegularDay-formula)

Answer 2

I'm going to assume your shift start and shift end are date-times, because otherwise you have no way to tell if your shift is on Saturday at all.

The critical calculation is to establish a date-time value when overtime is to begin in relation to your given Start and End date-times. After that all the calculations are pretty easy. So, in a given week if a shift starts on a Saturday or Sunday overtime begins on the previous Saturday, but for Monday - Friday shifts it starts on the NEXT Saturday. The following odd formula finds the prior Saturday for Mon-Fri, but finds the previous week's Saturday if today is Saturday or Sunday. Then we add one week & 12 hours to get the correct overtime start:

=Int((B2-2)/7)*7 + Date(1900,1,7) + Time(12,0,0)

We'll store that in B6. In this formula B2 is the given shift start date-time. Note that we could more simply say =Int((B2-2)/7)*7 + 7.5.

Now you did not mention whether breaks were paid or not - I'm going to assume they are, because if not you have another complication where you cannot tell whether the break was taken Before or After noon on Saturday and cannot tell whether to deduct the hour or time and a half.

With this date-time in B6 it is simple to calculate overtime hours:

=(MAX(B4,B6)-MAX(B2,B6))*24

...and I'll leave it to you to put it all together.

Here are some examples with the calculated overtime hours:

The first two columns show a Saturday start, the first goes the full day and is credited with 6 hours of overtime. The second goes from 8:00 until noon and is credited with no overtime. The third example is on Sunday, which I have assumed is time and a half all day. The fourth and final example is a Monday shift which does show any overtime because they would have to work until the next Saturday to get it.