Spreadsheet: 821317d1678618652-need-a-formula-to-calculate-overtime-based-on-a-shift-schedule-hour-calc
Sheet: Hours

Employee | Date in | Time in | Date out | Day | Time out | Net hours | Break time | Normal hours | Overtime | Double overtime |  |  | Shift Monday-Sunday | Start | End | Deduct Break | Normal hours | Overtime | Double overtime
Employee 1 | 2023-03-01 00:00:00 | 06:00:00 | 2023-03-01 00:00:00 |  | 08:30:00 | 2.5 | 0 |  |  |  |  |  | Normal hours (between 07.00-15.00) | 07:00:00 | 15:00:00
Employee 2 | 2023-03-01 00:00:00 | 06:00:00 | 2023-03-01 00:00:00 |  | 08:30:00 | 2.5 | 0 |  |  |  |  |  | Overtime (between 15.00-21.00) | 15:00:00 | 21:00:00
Employee 3 | 2023-03-01 00:00:00 | 06:00:00 | 2023-03-01 00:00:00 |  | 08:30:00 | 2.5 | 0 |  |  |  |  |  | Double overtime (between 21.00-07.00) | 21:00:00 | 07:00:00
Employee 4 | 2023-03-01 00:00:00 | 06:00:00 | 2023-03-01 00:00:00 |  | 08:30:00 | 2.5 | 0
Employee 5 | 2023-03-01 00:00:00 | 06:30:00 | 2023-03-01 00:00:00 |  | 14:30:00 | 7.5 | 0.5
Employee 1 | 2023-03-02 00:00:00 | 07:00:00 | 2023-03-02 00:00:00 |  | 10:00:00 | 3 | 0
Employee 2 | 2023-03-02 00:00:00 | 07:00:00 | 2023-03-02 00:00:00 |  | 10:00:00 | 3 | 0
Employee 3 | 2023-03-02 00:00:00 | 07:00:00 | 2023-03-02 00:00:00 |  | 19:00:00 | 11 | 1 |  |  |  |  |  | Shift Monday-Friday | Start | End
Employee 4 | 2023-03-02 00:00:00 | 07:00:00 | 2023-03-02 00:00:00 |  | 19:00:00 | 11 | 1 |  |  |  |  |  | Normal hours (between 07.00-15.00) | 07:00:00 | 15:00:00
Employee 5 | 2023-03-02 00:00:00 | 07:00:00 | 2023-03-02 00:00:00 |  | 19:00:00 | 11 | 1 |  |  |  |  |  | Overtime (between 15.00-21.00) | 15:00:00 | 21:00:00
Employee 1 | 2023-03-03 00:00:00 | 07:00:00 | 2023-03-03 00:00:00 |  | 19:00:00 | 11 | 1 |  |  |  |  |  | Double overtime (between 21.00-06.00) | 21:00:00 | 06:00:00
Employee 2 | 2023-03-03 00:00:00 | 07:00:00 | 2023-03-03 00:00:00 |  | 19:00:00 | 11 | 1 |  |  |  |  |  | Overtime (between 06.00-07.00) | 06:00:00 | 07:00:00
Employee 3 | 2023-03-03 00:00:00 | 07:00:00 | 2023-03-03 00:00:00 |  | 08:00:00 | 1 | 0
Employee 4 | 2023-03-03 00:00:00 | 07:00:00 | 2023-03-03 00:00:00 |  | 09:00:00 | 2 | 0
Employee 5 | 2023-03-03 00:00:00 | 07:00:00 | 2023-03-03 00:00:00 |  | 09:00:00 | 2 | 0 |  |  |  |  |  | Shift Saturday | Start | End
Employee 1 | 2023-03-04 00:00:00 | 07:00:00 | 2023-03-04 00:00:00 |  | 10:00:00 | 3 | 0 |  |  |  |  |  | Overtime (between 07.00-12.00) | 07:00:00 | 12:00:00
Employee 2 | 2023-03-04 00:00:00 | 07:00:00 | 2023-03-04 00:00:00 |  | 11:00:00 | 4 | 0 |  |  |  |  |  | Double overtime (between 12.00-07.00) | 12:00:00 | 06:00:00
Employee 3 | 2023-03-04 00:00:00 | 07:00:00 | 2023-03-04 00:00:00 |  | 12:00:00 | 4.5 | 0.5
Employee 4 | 2023-03-04 00:00:00 | 07:00:00 | 2023-03-04 00:00:00 |  | 12:30:00 | 5 | 0.5 |  |  |  |  |  | Shift Sunday | Start | End
Employee 5 | 2023-03-04 00:00:00 | 07:00:00 | 2023-03-04 00:00:00 |  | 15:00:00 | 7.5 | 0.5 |  |  |  |  |  | Double Overtime (between 00.00-00.00) | 00:00:00 | 00:00:00
Employee 1 | 2023-03-05 00:00:00 | 07:00:00 | 2023-03-05 00:00:00 |  | 10:00:00 | 3 | 0
Employee 2 | 2023-03-05 00:00:00 | 07:00:00 | 2023-03-05 00:00:00 |  | 11:00:00 | 4 | 0
Employee 3 | 2023-03-05 00:00:00 | 07:00:00 | 2023-03-05 00:00:00 |  | 12:00:00 | 4.5 | 0.5
Employee 4 | 2023-03-05 00:00:00 | 07:00:00 | 2023-03-05 00:00:00 |  | 12:30:00 | 5 | 0.5
Employee 5 | 2023-03-05 00:00:00 | 07:00:00 | 2023-03-05 00:00:00 |  | 15:00:00 | 7.5 | 0.5