Spreadsheet: 831232d1685520166-employee-hours-count-towards-contracted-hours-anything-after-overtime-nested-ifs-payroll-hours-aligw
Sheet: Sheet1

 |  |  |  | ANNUAL LEAVE (FULL DAY) = AL
 |  |  |  | ANNUAL LEAVE (HALF DAY) = AL.5
 |  |  |  | SICK LEAVE = SL |  |  |  |  |  |  | PERIOD 10 wk18
 |  |  |  |  |  |  |  |  |  |  | Week 18
 |  |  |  | BANK HOLIDAY = BH |  |  |  |  |  |  | Mon |  | Tue |  | Wed |  | Thu |  | Fri |  | Sat |  | Sun |  |  |  |  |  |  |  |  |  |  |  |  |  |  | WEEK 18
 |  |  |  | PARENTAL LEAVE = PL |  |  |  |  |  |  | 2023-05-01 00:00:00 |  | 2023-05-02 00:00:00 |  | 2023-05-03 00:00:00 |  | 2023-05-04 00:00:00 |  | 2023-05-05 00:00:00 |  | 2023-05-06 00:00:00 |  | 2023-05-07 00:00:00
 |  | Employee ID | First Name | Surname | Sick Leave Used | Parental Leave Used | AL | AL Starting Balance | AL Booked | AL Ending Balance | Shift | O/T | Shift | O/T | Shift | O/T | Shift | O/T | Shift | O/T | First 4 hrs | O/T | O/T | Holidays (day count) | Bank Holidays | [NAME] (day count) | Sick helper formula | [NAME] (day count) | Parental Leave (unpaid) | Annual Leave - half day | Annual Leave - full day | Total Hrs Excl Sick and double OT | BASIC | OT 1.5 | OT 1.5 HELPER | OT 2 | OT 2 HELPER | TOTAL
 |  | 1 |  |  |  |  |  | 21 |  |  | SL |  | 8 |  | 8 |  | 8 |  | 7.5 |  | 4 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | total should be 35.5 hrs because of the sick leave (the total doesn't match this statement because I'm not sure how to pull together the formula)
 |  | 2 |  |  |  |  |  | 21 |  |  | PL |  | 8 |  | 8 |  | 8 |  | 7.5 |  | 4 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | same as above because of the parental leave
 |  | 3 |  |  |  |  |  | 21 |  |  | BH |  | 8 |  | 8 |  | 8 |  | 7.5 |  | 4 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | here the result should be 45.5 (6 in OT1.5 and the 39.5 basic)
 |  | 4 |  |  |  |  |  | 21 |  |  | AL |  | 8 |  | 8 |  | 8 |  | 7.5 |  | 4 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | the result will be the same as the one above (for annual leave and bank holiday the same rules apply)
 |  | 5 |  |  |  |  |  | 21 |  |  | 8 |  | 8 |  | 8 |  | 8 |  | 6 |  | 4 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Here the result should be 43.25 in 'TOTAL' (this is because on [NAME] the worked hours are only 6 and therefore we need to count towards contracted hours first before we add anything into overtime)
 |  | 6 |  |  |  |  |  | 21 |  |  | 8 |  | 8 |  | 8 |  | 8 |  | 7.5 |  |  | 2 | 2 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Employees can work double overtime as well (on Sat afternoon and Sun). In this case the total will be 47.5 (39.5 and 4 hours double overtime = 8)
 |  | 7 |  |  |  |  |  | 21 |  |  | BH |  | 8 |  | 8 |  | 8 |  | 6 |  |  | 2
 |  | 8 |  |  |  |  |  | 21 |  |  | BH |  | 8 |  | 8 |  | 8 |  | 7.5
 |  | 9 |  |  |  |  |  | 21 |  |  | BH |  | 8 |  | 8 |  | 8 |  | 7.5
 |  | 10 |  |  |  |  |  | 21 |  |  | BH |  | 8 |  | 8 |  | 8 |  | 7.5
 |  | 11 |  |  |  |  |  | 21 |  |  | BH |  | 8 |  | 8 |  | 8 |  | 7.5
 |  | 12 |  |  |  |  |  | 21 |  |  | BH |  | 8 |  | 8 |  | 8 |  | 7.5
 |  | 13 |  |  |  |  |  | 21 |  |  | BH |  | 8 |  | 8 |  | 8 |  | 7.5
 |  | 14 |  |  |  |  |  | 21 |  |  | BH |  | 8 |  | 8 |  | 8 |  | 7.5
 |  | 15 |  |  |  |  |  | 21 |  |  | BH |  | 8 |  | 8 |  | 8 |  | 7.5
 |  | 16 |  |  |  |  |  | 21 |  |  | BH |  | 8 |  | 8 |  | 8 |  | 7.5
 |  | 17 |  |  |  |  |  | 21 |  |  | BH |  | 8 |  | 8 |  | 8 |  | 7.5
 |  | 18 |  |  |  |  |  | 21 |  |  | BH |  | 8 |  | 8 |  | 8 |  | 7.5
 |  | 19 |  |  |  |  |  | 21 |  |  | BH |  | 8 |  | 8 |  | 8 |  | 7.5
 |  | 20 |  |  |  |  |  | 21 |  |  | BH |  | 8 |  | 8 |  | 8 |  | 7.5
 |  |  |  |  |  |  |  |  |  | TOTAL INCLUDING ALL
 |  |  |  |  |  |  |  |  |  | FINAL FORMULA TO BE IN TOTAL
 |  |  |  |  |  |  |  |  |  | OT 1.5
 |  |  |  |  |  |  |  |  |  | OT 1.5 HELPER
 |  |  |  |  |  |  |  |  |  | OT 2