Hi There, Can someone help me with here - I'm struggling hard from last 2 days to convert my input to output desired format.
Here is my input table:
| Employee Name | Location | Check-in | Check-out | Total Hours | Unpaid break | Payable Hours | Status | Overtime |
| Person A | Willits, CA | 02/26/2023 06:23 PM | 02/27/2023 12:05 PM | 14:14 | 00:00 | 08:00 | Weekend, Present | 14:14 |
| Person A | Willits, CA | 02/27/2023 06:55 PM | 02/28/2023 09:12 AM | 11:12 | 00:00 | 08:00 | Present | 03:12 |
| Person A | Willits, CA | 02/28/2023 06:44 PM | 03-01-2023 05:02 | 09:05 | 00:00 | 08:00 | Present | 01:05 |
| Person A | Willits, CA | 03-01-2023 19:26 | 03-02-2023 06:06 | 06:46 | 00:00 | 06:46 | Present | |
| Person A | Willits, CA | 03-02-2023 19:00 | 03-03-2023 06:28 | 08:54 | 00:00 | 08:00 | Present | 00:54 |
| Person A | Willits, CA | 03-03-2023 20:00 | 03-04-2023 06:15 | 08:35 | 00:00 | 08:00 | Present | 00:35 |
| Person A | Willits, CA | 03-04-2023 21:27 | 03-04-2023 23:57 | 02:30 | 00:00 | 08:00 | Weekend, Present | 02:30 |
| Person A | Willits, CA | 03-05-2023 18:41 | 03-06-2023 06:09 | 06:46 | 00:00 | 08:00 | Weekend, Present | 06:46 |
| Person A | Willits, CA | 03-06-2023 19:05 | 03-07-2023 05:50 | 09:31 | 00:00 | 08:00 | Present | 01:31 |
| Person A | Willits, CA | 03-07-2023 18:36 | 03-08-2023 05:48 | 09:51 | 00:00 | 08:00 | Present | 01:51 |
| Person A | Willits, CA | 03-08-2023 18:38 | 03-09-2023 05:02 | 09:34 | 00:00 | 08:00 | Present | 01:34 |
| Person A | Willits, CA | 03-09-2023 18:48 | 03-10-2023 10:29 | 12:40 | 00:00 | 08:00 | Present | 04:40 |
| Person A | Willits, CA | 03-10-2023 19:02 | 03-11-2023 10:55 | 11:50 | 00:00 | 08:00 | Present | 03:50 |
| Person A | Willits, CA | 03-11-2023 19:00 | 03-12-2023 11:00 | 09:41 | 00:00 | 08:00 | Weekend, Present | 09:41 |
| Person A | Willits, CA | 03-05-2023 18:41 | 03-06-2023 06:09 | 06:46 | 00:00 | 08:00 | Weekend, Present | 06:46 |
| Person A | Willits, CA | 03-06-2023 19:05 | 03-07-2023 05:50 | 09:31 | 00:00 | 08:00 | Present | 01:31 |
| Person A | Willits, CA | 03-07-2023 18:36 | 03-08-2023 05:48 | 09:51 | 00:00 | 08:00 | Present | 01:51 |
| Person A | Willits, CA | 03-08-2023 18:38 | 03-09-2023 05:02 | 09:34 | 00:00 | 08:00 | Present | 01:34 |
| Person A | Willits, CA | 03-09-2023 18:48 | 03-10-2023 10:29 | 12:40 | 00:00 | 08:00 | Present | 04:40 |
| Person A | Willits, CA | 03-10-2023 19:02 | 03-11-2023 10:55 | 11:50 | 00:00 | 08:00 | Present | 03:50 |
| Person A | Willits, CA | 03-11-2023 19:00 | 03-12-2023 11:00 | 09:41 | 00:00 | 08:00 | Weekend, Present | 09:41 |
I'm looking for the below output table. Where total time in the input table is categorized into Payable Hours and Overtime. What i'm looking for is to convert the overtime from above table into two categories 1.5x and 2.0 based on these logics.
1) 1.5x hourly rate for work over 8 hours in a workday and 40 hours in a workweek
2) 1.5x hourly rate for the first 8 hours on the seventh consecutive day of work
3) 2x hourly rate for work over 12 hours in a workday
4) 2x hourly rate for work over 8 hours on the seventh consecutive day of work
Based on the above logics you see that the below table has the 1.5x and 2.0x columns satisfying the all the conditions.
Final Output
| First Name | Clock In Date | Clock In Time | Clock Out Date | Clock Out Time | Timezone | Total Hours | Deductions | Standard Hours | Overtime 1.5x Hours | Overtime 2.0x Hours |
| Person A | 26-02-2023 | 04:53:00 | 26-02-2023 | 22:35:00 | PDT | 14.25 | 0 | 8 | 4 | 2.25 |
| Person A | 27-02-2023 | 05:25:00 | 27-02-2023 | 19:42:00 | PDT | 11.2 | 0 | 8 | 3.2 | 0 |
| Person A | 28-02-2023 | 05:14:00 | 28-02-2023 | 15:32:00 | PDT | 9.1 | 0 | 8 | 1.1 | 0 |
| Person A | 01-03-2023 | 05:55:00 | 01-03-2023 | 16:37:00 | PDT | 6.8 | 0 | 6.8 | 0 | 0 |
| Person A | 02-03-2023 | 05:30:00 | 02-03-2023 | 17:01:00 | PDT | 9 | 0 | 8 | 1 | 0 |
| Person A | 03-03-2023 | 06:30:00 | 03-03-2023 | 16:47:00 | PDT | 8.63 | 0 | 1.2 | 7.43 | 0 |
| Person A | 04-03-2023 | 07:57:00 | 04-03-2023 | 10:28:00 | PDT | 2.52 | 0 | 0 | 2.52 | 0 |
| Person A | 05-03-2023 | 05:11:00 | 05-03-2023 | 16:40:00 | PDT | 6.78 | 0 | 6.78 | 0 | 0 |
| Person A | 06-03-2023 | 05:35:00 | 06-03-2023 | 16:26:00 | PDT | 9.63 | 0 | 8 | 1.63 | 0 |
| Person A | 07-03-2023 | 05:05:00 | 07-03-2023 | 16:23:00 | PDT | 9.99 | 0 | 8 | 1.99 | 0 |
| Person A | 08-03-2023 | 05:08:00 | 08-03-2023 | 15:35:00 | PDT | 9.63 | 0 | 8 | 1.63 | 0 |
| Person A | 09-03-2023 | 05:18:00 | 09-03-2023 | 21:01:00 | PDT | 12.75 | 0 | 8 | 4 | 0.75 |
| Person A | 10-03-2023 | 05:32:00 | 10-03-2023 | 21:26:00 | PDT | 11.85 | 0 | 1.22 | 10.63 | 0 |
| Person A | 11-03-2023 | 05:30:00 | 11-03-2023 | 21:30:00 | PDT | 9.68 | 0 | 0 | 8 | 1.68 |
