Forum Discussion
Calculating an hourly penalty rate within a shift that may have non-penalty hours within that shift.
In the attached version, I have cleared the start and end times for day you don't work,
Instead of first calculating the penalty hours, I have first calculated the normal hours, then calculated penalty hours as total - normal.
Thanks for your kind offer, much appreciated, but that's not necessary.
Hi Hans,
I sincerely need your help again, if possible. I do feel I am intruding on your time and knowledge and for that reason I am uncomfortable, but in this instance I look to you as my on line mentor 🙂
I have 2 issues I am struggling with, and I have attached the workbook file;
Issue #1.
I refer to the worksheet "Penalty Rate Calculator". It has occurred to me that I did not include the Voluntary Overtime worked into the penalty rate hours. I went ahead and gave it my best shot thinking I should be able to insert the same formulas from the 2nd. half but it is not working for me.
Where am I going wrong? And on that basis, so I can learn more, is it possible to explain how you put the formulas together? If that is touching on your intellectual property I understand and withdraw that request.
Issue #2.
I refer to the worksheet "Work Calculator", specifically Broken Shift Penalty, (cells highlighted in yellow).
A broken shift is where I drive the bus for maybe 4 hours in the morning peak period. I then have maybe a 4 hour break, then re-commence my shift driving for maybe 4 hours in the evening peak time. Note these drive times and subsequent break may vary.
I am then paid an increase in my hourly rate if I drive a bus for longer than 9.5 hours. (an explanation is entered in "Calculator Parameters" A45 - ITEM 7. BROKEN SHIFTS
Basically if I drive a bus for 9.5 to 10.5 hours I am paid at a rate of time and a half. Thereafter I am paid at double time.
I have created a number of different scenarios in Work Calculator.
Thanking you in advance,
Michael
- Hi Hans, oopsie I made a typo error.
It should read;
"If you start work at 1:15PM on a Monday afternoon and finish after 8:00PM you are paid a 15% penalty rate on the total shift worked. For example if you finish at 8:15PM you are entitled to a full payment of 15% on the total shift worked."
Furthermore delete the issue regarding calculation for overtime. I have since had this clarified by the union delegate that if the work time is not built into the initial shift, any overtime added to the shift is not considered a part of the penalty rate entitlement.
That is if the shift starts at 1:15PM and finishes at 7:59PM, and I then work for 30mins extra as overtime, and finish at 8:29PM I am not entitled to any penalty rate.
This bus driver award is so complicated !!!
Thanks again Hans. You write
"If you start work at 11:15PM on a Monday afternoon and finish after 8:00PM you are paid a 15% penalty rate on the total shift worked. For example if you finish at 8:15PM you are entitled to a full payment of 15% on the total shift worked.
Furthermore this also applies to Overtime where you may finish after 8:00PM. For example if you start work at 11:59AM and finish at 8:59PM you are not entitled to any 15% Penalty Rate. However if you then do overtime of say 15 minutes, and therefore finish at 8:14PM you are then entitled to the 15% Penalty Rate for that total shift worked"
Isn't that contradictory? Did you mean "start work at 11:59AM and finish at 7:59PM"?
Hi Hans, I do hope this message finds you in good health.
I have been plugging away at my spreadsheet slowly slowly when I have the time.
Since our last correspondence I have been able to buy a new laptop and I have subscribed to 365 which has made working on the spreadsheet a lot more easier and enjoyable.
I now have noticed 2 mistakes that I provided to you.
In both cases I have tried to rectify them but I have had to surrender my abilities and seek you help.
Issue #1. I have provided you with incorrect information regarding the "Broken/Late Penalty".
This penalty is now defined as follows;
1. If you start work after 8PM and finish before 7:00AM you are paid 15% penalty rate on the total shift hours worked. For example starting at 8:30PM on a Monday night and finishing at 4:30AM on the Tuesday morning you are paid 8 hours plus a full 15% penalty addition on those 8 hours.
2. If you start work before 7:00AM you are only paid a 15% penalty rate on those hours worked. For example if you start work at 4:30AM on a Monday morning and finish at 12:30PM on the Monday afternoon you only receive a 15% loading on the 2.5 hours worked prior to 7:00AM.
3. If you start work at 11:15PM on a Monday afternoon and finish after 8:00PM you are paid a 15% penalty rate on the total shift worked. For example if you finish at 8:15PM you are entitled to a full payment of 15% on the total shift worked.
Furthermore this also applies to Overtime where you may finish after 8:00PM. For example if you start work at 11:59AM and finish at 8:59PM you are not entitled to any 15% Penalty Rate. However if you then do overtime of say 15 minutes, and therefore finish at 8:14PM you are then entitled to the 15% Penalty Rate for that total shift worked.
I have attached the spreadsheet and highlighted the relevant tab sheets in yellow (Penalty Rate Calculator and Work Calculator) along with the rows applicable to this inquiry.
ISSUE #2
The 2nd problem I am encountering is related to a broken shift. For a broken shift you are only entitled to overtime from 8.25 hours worked to 9.5 hours. After that the broken shift penalty takes precedence. That is you are not paid overtime as the 1.5 and Double time rates cut in. I am struggling with that problem also. Please refer to Rows 13 and 14 of the Work Calculator sheet for this.
Please feel free to change/edit whatever you deem necessary.
Also note when you open you may be prompted to update links, that is to a tax file I have but it is not applicable to this issue/s.
Thanking you once again ... Michael
Mike_Geraghty Hi Mike,
That's very nice of your wife. I live in Europe, so it won't be possible, but please tell her that I really appreciate the idea!
- Hi Hans,
I have noticed that my previous reply a few days ago, in response to your help, may not have successfully posted.
Nonetheless once again thank you. I did show my wife how much you have helped us, and as she is Malaysian she wants to also offer you her appreciation in the only way she knows, that being sending you her signature chicken curry 🙂
I did tell her that I do not think you reside in Australia so it may not be possible, but I was touched by her understanding of the immense amount of knowledge you have, and how you helping me can ensure I am paid my correct wage.
I will soon be able to start a full audit of my payslips over the previous 10 months, but already I have picked up discrepancies.
My employer does, in no way, have any ill intent in this regard. I believe they do not have a salary/wage software that is aligned specifically to our complex industry award.
Once again thank you ... Michael Issue #2: see the attached version.
Hi Mike.
About Issue #1: you did it correctly, but you made one tiny mistake: in the penalty formula for Sunday, you multiply with 0 to cancel the penalty. In column U, you then filled the formula down from Sunday, causing ALL penalties in that column to be canceled.
Here follows a lengthy explanation of the formula.
The formula in columns F, M and T calculate normal working hours. In F7:
=IF(C7="","",24*(MAX(MIN(D7+(D7<C7),TIME(20,0,0))-MAX(C7,TIME(7,0,0)),0)+IF(D7<C7,MAX(D7-TIME(7,0,0),0),0)))
The formula first checks if the start time in C7 has been filled in. If not, it returns a blank "".
Otherwise, it calculates the normal working time, and multiplies the result with 24 to convert the time to decimal hours. The actual calculation consists of two parts:
MAX(MIN(D7+(D7<C7),TIME(20,0,0))-MAX(C7,TIME(7,0,0)),0)
and
IF(D7<C7,MAX(D7-TIME(7,0,0),0),0)
In the first part:
MIN(D7+(D7<C7),TIME(20,0,0)) is the end time, but if if is later then 20:00 (8 PM), take 20:00.
MAX(C7,TIME(7,0,0)) is the start time, but if it is earlier than 7:00 (7 AM), take 7:00.
We subtract the start time from the end time, but if the result is negative (because working time falls outside normal hours), take 0.
The second part only applies if end time is before start time in clock time, i.e. start time is before midnight and end time is after midnight, for example start=23:00 and end=8:00. In that case, MAX(D7-TIME(7,0,0) calculates the time worked after 7:00 (7 AM). If this is negative because end time was before 7:00, take 0.
I'll tackle Issue #2 in my next reply.
