Spreadsheet: 855829d1705321511-calculating-work-hours-between-two-datetimes-tab_shift_pattern-hd
Sheet: Sheet1

Day of week | Start date of shift pattern | End date of shift pattern | Weekday | Work time start | Work time end |  |  | Stop line clearance | Start next line clearance | StartDates | StartTimes | EndDates | EndTimes | Work time hours between Stop line clearance and Start next line clearance | ChatGPT formula1 | ChatGPT formula2 |  |  |  |  |  |  | Stop line clearance | Start next line clearance
Monday | 2019-01-01 00:00:00 | 2019-12-31 00:00:00 | 1 | 06:30:00 | 22:30:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2021-07-15 20:21:38 | 2021-08-03 07:02:58
Tuesday | 2019-01-01 00:00:00 | 2019-12-31 00:00:00 | 2 | 06:30:00 | 22:30:00 |  |  | 2021-08-18 14:49:56 | 2021-08-18 16:13:00 | 2021-08-18 00:00:00 | 14:49:56 | 2021-08-18 00:00:00 | 2021-08-18 01:23:04 |  |  | Basic Test |  |  |  |  |  |  | 2021-08-18 14:49:56 | 2021-08-18 16:13:00
Wednesday | 2019-01-01 00:00:00 | 2019-12-31 00:00:00 | 3 | 06:30:00 | 22:30:00 |  |  | 2023-05-12 00:02:40 | 2023-05-13 07:52:28 | 2023-05-12 00:00:00 | 00:02:40 | 2023-05-13 00:00:00 | 2023-05-13 07:49:48 |  |  | Basic Test |  |  |  |  |  |  | 2023-05-12 00:02:40 | 2023-05-13 07:52:28
Thursday | 2019-01-01 00:00:00 | 2019-12-31 00:00:00 | 4 | 06:30:00 | 22:30:00 |  |  | 2023-06-06 17:53:44 | 2023-06-06 20:19:12 | 2023-06-06 00:00:00 | 17:53:44 | 2023-06-06 00:00:00 | 2023-06-06 02:25:28 |  |  | Basic Test |  |  |  |  |  |  | 2023-06-06 17:53:44 | 2023-06-06 20:19:12
Friday | 2019-01-01 00:00:00 | 2019-12-31 00:00:00 | 5 | 06:30:00 | 14:30:00 |  |  | 2019-01-04 14:03:00 | 2019-01-05 07:00:00 | 2019-01-04 00:00:00 | 14:03:00 | 2019-01-05 00:00:00 | 2019-01-04 16:57:00 |  |  | Basic Test |  |  |  |  |  |  | 2019-01-04 14:03:00 | 2019-01-05 07:00:00
Saturday | 2019-01-01 00:00:00 | 2019-12-31 00:00:00 | 6 | 00:00:00 | 00:00:00 |  |  | 2019-01-05 12:55:00 | 2019-01-07 07:11:00 | 2019-01-05 00:00:00 | 12:55:00 | 2019-01-07 00:00:00 | 2019-01-06 18:16:00 |  |  | Basic Test |  |  |  |  |  |  | 2019-01-05 12:55:00 | 2019-01-07 07:11:00
Sunday | 2019-01-01 00:00:00 | 2019-12-31 00:00:00 | 7 | 00:00:00 | 00:00:00 |  |  | 2019-01-07 16:53:00 | 2019-01-07 17:45:00 | 2019-01-07 00:00:00 | 16:53:00 | 2019-01-07 00:00:00 | 2019-01-07 00:52:00 |  |  | Basic Test |  |  |  |  |  |  | 2019-01-07 16:53:00 | 2019-01-07 17:45:00
Monday | 2020-01-01 00:00:00 | 2020-04-12 00:00:00 | 1 | 00:00:00 | 23:59:59.900000 |  |  | 2019-01-08 17:46:00 | 2019-01-08 18:30:00 | 2019-01-08 00:00:00 | 17:46:00 | 2019-01-08 00:00:00 | 2019-01-08 00:44:00 |  |  | Basic Test |  |  |  |  |  |  | 2019-01-08 17:46:00 | 2019-01-08 18:30:00
Tuesday | 2020-01-01 00:00:00 | 2020-04-12 00:00:00 | 2 | 00:00:00 | 23:59:59.900000 |  |  | 2019-01-09 16:10:00 | 2019-01-09 16:49:00 | 2019-01-09 00:00:00 | 16:10:00 | 2019-01-09 00:00:00 | 2019-01-09 00:39:00 |  |  | Basic Test |  |  |  |  |  |  | 2019-01-09 16:10:00 | 2019-01-09 16:49:00
Wednesday | 2020-01-01 00:00:00 | 2020-04-12 00:00:00 | 3 | 00:00:00 | 23:59:59.900000 |  |  | 2019-01-10 12:18:00 | 2019-01-10 13:36:00 | 2019-01-10 00:00:00 | 12:18:00 | 2019-01-10 00:00:00 | 2019-01-10 01:18:00 |  |  | Basic Test |  |  |  |  |  |  | 2019-01-10 12:18:00 | 2019-01-10 13:36:00
Thursday | 2020-01-01 00:00:00 | 2020-04-12 00:00:00 | 4 | 00:00:00 | 23:59:59.900000 |  |  | 2019-01-11 11:13:00 | 2019-01-12 06:47:00 | 2019-01-11 00:00:00 | 11:13:00 | 2019-01-12 00:00:00 | 2019-01-11 19:34:00 |  |  | Basic Test |  |  |  |  |  |  | 2019-01-11 11:13:00 | 2019-01-12 06:47:00
Friday | 2020-01-01 00:00:00 | 2020-04-12 00:00:00 | 5 | 00:00:00 | 15:00:00 |  |  | 2019-01-12 15:27:00 | 2019-01-14 05:51:00 | 2019-01-12 00:00:00 | 15:27:00 | 2019-01-14 00:00:00 | 2019-01-13 14:24:00 |  |  | Basic Test |  |  |  |  |  |  | 2019-01-12 15:27:00 | 2019-01-14 05:51:00
Saturday | 2020-01-01 00:00:00 | 2020-04-12 00:00:00 | 6 | 00:00:00 | 00:00:00 |  |  | 2019-01-15 00:45:00 | 2019-01-15 05:49:00 | 2019-01-15 00:00:00 | 00:45:00 | 2019-01-15 00:00:00 | 2019-01-15 05:04:00 |  |  | Basic Test |  |  |  |  |  |  | 2019-01-15 00:45:00 | 2019-01-15 05:49:00
Sunday | 2020-01-01 00:00:00 | 2020-04-12 00:00:00 | 7 | 23:30:00 | 23:59:59.900000 |  |  | 2019-01-15 22:11:00 | 2019-01-16 05:35:00 | 2019-01-15 00:00:00 | 22:11:00 | 2019-01-16 00:00:00 | 2019-01-15 07:24:00 |  |  | Basic Test |  |  |  |  |  |  | 2019-01-15 22:11:00 | 2019-01-16 05:35:00
Monday | 2020-04-13 00:00:00 | 2021-08-14 00:00:00 | 1 | 06:30:00 | 22:30:00 |  |  | 2019-01-16 14:09:00 | 2019-01-16 14:34:00 | 2019-01-16 00:00:00 | 14:09:00 | 2019-01-16 00:00:00 | 2019-01-16 00:25:00 |  |  | Basic Test |  |  |  |  |  |  | 2019-01-16 14:09:00 | 2019-01-16 14:34:00
Tuesday | 2020-04-13 00:00:00 | 2021-08-14 00:00:00 | 2 | 06:30:00 | 22:30:00 |  |  | 2019-01-16 20:50:00 | 2019-01-17 06:13:00 | 2019-01-16 00:00:00 | 20:50:00 | 2019-01-17 00:00:00 | 2019-01-16 09:23:00 |  |  | Basic Test |  |  |  |  |  |  | 2019-01-16 20:50:00 | 2019-01-17 06:13:00
Wednesday | 2020-04-13 00:00:00 | 2021-08-14 00:00:00 | 3 | 06:30:00 | 22:30:00
Thursday | 2020-04-13 00:00:00 | 2021-08-14 00:00:00 | 4 | 06:30:00 | 22:30:00
Friday | 2020-04-13 00:00:00 | 2021-08-14 00:00:00 | 5 | 06:30:00 | 14:30:00
Saturday | 2020-04-13 00:00:00 | 2021-08-14 00:00:00 | 6 | 00:00:00 | 00:00:00
Sunday | 2020-04-13 00:00:00 | 2021-08-14 00:00:00 | 7 | 00:00:00 | 00:00:00
Monday | 2021-08-15 00:00:00 | 2022-04-17 00:00:00 | 1 | 00:00:00 | 23:59:59.900000
Tuesday | 2021-08-15 00:00:00 | 2022-04-17 00:00:00 | 2 | 00:00:00 | 23:59:59.900000
Wednesday | 2021-08-15 00:00:00 | 2022-04-17 00:00:00 | 3 | 00:00:00 | 23:59:59.900000
Thursday | 2021-08-15 00:00:00 | 2022-04-17 00:00:00 | 4 | 00:00:00 | 23:59:59.900000
Friday | 2021-08-15 00:00:00 | 2022-04-17 00:00:00 | 5 | 06:30:00 | 19:30:00
Saturday | 2021-08-15 00:00:00 | 2022-04-17 00:00:00 | 6 | 00:00:00 | 00:00:00
Sunday | 2021-08-15 00:00:00 | 2022-04-17 00:00:00 | 7 | 20:30:00 | 23:59:59.900000
Monday | 2022-04-18 00:00:00 |  | 1 | 00:00:00 | 23:59:59.900000
Tuesday | 2022-04-18 00:00:00 |  | 2 | 00:00:00 | 23:59:59.900000
Wednesday | 2022-04-18 00:00:00 |  | 3 | 00:00:00 | 23:59:59.900000
Thursday | 2022-04-18 00:00:00 |  | 4 | 00:00:00 | 23:59:59.900000
Friday | 2022-04-18 00:00:00 |  | 5 | 00:00:00 | 19:30:00
Saturday | 2022-04-18 00:00:00 |  | 6 | 00:00:00 | 00:00:00
Sunday | 2022-04-18 00:00:00 |  | 7 | 20:30:00 | 23:59:59.900000