Forum Discussion
Calculating an hourly penalty rate within a shift that may have non-penalty hours within that shift.
- Mar 27, 2022
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.
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 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
- HansVogelaarSep 16, 2022MVP
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"?
- Mike_GeraghtySep 17, 2022Copper ContributorHi 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.