Spreadsheet: 805774d1668906164-how-to-enter-in-an-if-function-to-capture-cell-data-pay-calculator_rev-08a
Sheet: Calculator Parameters

HOURS OF WORK, OVERTIME & PENALTY
 |  | Hours | Amount | Pay Rate | Trainee Bus Operator | Bus Operator -Level 2 | Senior Bus Operator | Senior Bus Operator -Yard (SBOY) | [NAME]
ITEM 1. NORMAL WORK HOURS MON-FRI
(i)   Normal pay rate is 8hrs 15mins
08:00:00 | 16:15:00 |  | 1 |  | 28.7 | 30.8711 | 31.432 | 32.303 | 33.905
 | [NAME]
(ii)  Then time and a half for the next 3 hours |  |  | 1.5
(iii) Thereafter double time |  |  | 2
ITEM 2. SATURDAY
(i) The rate is time and a half |  |  | 1.5
08:00:00 | 16:15:00
 | [NAME]
(iii) Thereafter double time |  |  | 2
ITEM 3. SUNDAY
The rate is double time |  |  | 2
ITEM 4. ROSTERED DAY OFF and ALLOCATED DAY OFF
(i)  Monday - Friday
Normal rate of pay for 8hrs |  | 8 | 1
(ii) Saturday
Rate of pay is normal Saturday rate |  | 8 | 1.5
ITEM 5. ROSTERED DAY OFF CANCELLED
Monday - Friday
(i) The first 3 hours - time and a half |  | 3 | 1.5
Thereafter the rate is double time |  |  | 2
(ii) Saturday
The rate is double time |  |  | 2
ITEM 6. ALLOCATED DAY OFF CANCELLED
Monday - Friday
(i) The ADO is paid in full |  | 8 | 1
(ii) On top of that the first 3 hours is paid at the rate of -                     time and a half |  | 3 | 1.5
(iii) Thereafter the rate is double time |  |  | 2
Saturday
ITEM 7. BROKEN SHIFTS
Monday-Friday
(i) After a spread of driving time between 9.5 and 10.5 hours - time and a half |  |  | 1.5
(ii) After 10.5 hours - double time. |  |  | 2
Saturday & Public Holiday
(i)  The rate is double time |  |  | 2
NOTE: overtime is paid at 1.5 and not included in the spread hours
ITEM 8. SHIFT PENALTIES
Monday-Friday
(i) For time on duty between the hours of 8:00 pm and 7:00 am shall be paid 15% more than the normal pay rate, and calculated to the nearest quarter of an hour
20:00:00 | 07:00:00 |  | 0.15
(ii) High Capacity Allowance (B-Line & Articulated)
Shall be paid a set rate of $22.90/shift |  |  | 1 | 22.9
ITEM 9. TRAVEL TIME
Hours paid for travel to other depots |  | 1.25 | 1
ITEM 10. PUBLIC [NAME]
(i)  Monday-Friday
If required to work pay at the rate of time and a half |  |  | 1.5 |  | Note: I already factor in the 24mins deduction for ADO so should be 8 hrs
In addition also paid the public holiday rate up to 7.6hrs |  | 7.6 | 1
If not rostered to work the public holiday is paid normal rate of pay for 7.6hrs |  | 7.6 | 1
(ii)  Saturday |  |  | 1.5
When a Public Holiday falls on a Saturday, Bus Operators who are rostered off will qualify for payment if they have worked at least ten (10) Saturdays in the preceding twelve months. |  | 7.6 | 1
(iii)  Sunday
If required to work pay at the rate of double time |  |  | 2
In addition also paid the public holiday rate up to 7.6hrs |  | 7.6 | 1
ITEM 11. ANNUAL LEAVE
Normal rate of pay for 8.0 hrs |  | 8 | 1
Plus leave loading at 20% |  |  | 0.2
ITEM 12. SICK LEAVE
Normal rate of pay for 8.0 hrs |  | 8 | 1
ITEM 13. BUILD-UP TIME FOR ADO
Build-up time is based on a roster period of 20 work days calculated on a Monday - Friday week. Saturday and Sunday are not considered as work days |  | 8 | 1
Therefore to calculate build-up time use
08:00:00 | 16:15:00
The build-up time is calculated based on 19 days as work, and the 20th day is an ADO
Pay is based on a 38hr work week for the 4 weeks, that is the 20 day roster period
Therefore to make up the ADO, 2 hours is deducted from each of the 4 weeks in that roster period. |  | 02:00:00
As such 2 hours is deducted from weeks 1 to 3 which make up 5 working days per week
And then 2 hours is deducted from week 4 which makes up 4 working days.
Therfore for weeks 1 to 3 apply the factor of 0.4 (24mins)/day to the deduction for each day = 2 hrs/week |  | 00:24:00
Therefore each day should be 8hrs - 24mins |  | 07:36:00
Therfore for weeks 4 apply the factor of 0.5 (30mins)/day to the deduction for each day = 2 hrs/week |  | 00:30:00
IF THERE IS AN ADOC THE TIME IS NOT ALLOCATED |  | 0
ITEM 14. MAKE-UP TIME FOR 38HRS WORKED
If in the event 40 hours work time is not rostered for a 5 day week, then the emplyer must make up the shortfall. Sunday is not considered a normal work day. |  | 40