Spreadsheet: 820902d1678337954-calculate-difference-between-2-datetimes-considering-shift-timings-weekends-and-holidays-book1
Sheet: Sheet1

Requested | Solved | TAT in Hours |  |  | Holidays |  |  | Shift Timings | 04 PM to 03 AM
2023-01-01 01:36:00 | 2023-01-02 20:16:00 | desired output should be 4:16 hours for this case |  |  | 2023-01-16 00:00:00 |  |  | Working Days | Monday to Friday
2023-01-01 01:37:00 | 2023-01-02 14:50:00 |  |  |  | 2023-01-26 00:00:00
2023-01-02 09:43:00 | 2023-01-02 13:07:00 |  |  |  | 2023-02-20 00:00:00
2023-01-02 21:11:00 | 2023-01-03 10:39:00
2023-01-02 21:39:00 | 2023-01-03 10:04:00
2023-01-02 22:34:00 | 2023-01-04 15:00:00
2023-01-03 02:50:00 | 2023-01-03 09:39:00
2023-01-03 14:30:00 | 2023-01-06 18:00:00
2023-01-03 15:29:00 | 2023-01-05 11:09:00 |  |  |  |  | Question: | I have 2 columns in excel. One is for the requested date (column A) and other is for the solved date (column B). both columns format is in dd-mm-yyyy hh:[NAME]. I want to get difference between those datetime in hours.  My shift starts at 04 PM afternoon and ends at 03 AM in the next day early morning. Condition is that I want to consider difference with the reference of working shift only. Also exclude weekends and holidays from that.
2023-01-03 15:47:00 | 2023-01-03 16:51:00
2023-01-03 15:52:00 | 2023-01-03 16:17:00
2023-01-03 16:10:00 | 2023-01-03 18:04:00
2023-01-03 17:29:00 | 2023-01-03 18:11:00
2023-01-03 19:58:00 | 2023-01-04 17:35:00
2023-01-03 20:00:00 | 2023-01-04 09:42:00
2023-01-03 22:17:00 | 2023-01-04 12:01:00
2023-01-03 23:00:00 | 2023-01-04 12:15:00
2023-01-04 00:31:00 | 2023-01-10 16:23:00
2023-01-04 00:57:00 | 2023-01-05 18:58:00
2023-01-04 11:52:00 | 2023-01-04 14:40:00
2023-01-04 12:41:00 | 2023-01-04 18:46:00
2023-01-04 13:06:00 | 2023-01-04 18:49:00
2023-01-04 13:21:00 | 2023-01-04 18:52:00
2023-01-04 13:53:00 | 2023-01-05 11:11:00
2023-01-04 14:47:00 | 2023-01-05 18:24:00
2023-01-04 15:04:00 | 2023-01-06 17:37:00
2023-01-04 15:49:00 | 2023-01-04 19:20:00
2023-01-04 16:27:00 | 2023-01-04 19:29:00
2023-01-04 17:21:00 | 2023-01-06 18:01:00
2023-01-04 18:01:00 | 2023-01-06 11:30:00
2023-01-04 18:30:00 | 2023-01-04 19:40:00
2023-01-04 19:55:00 | 2023-01-05 09:43:00
2023-01-04 19:56:00 | 2023-01-05 17:41:00
2023-01-05 00:30:00 | 2023-01-05 09:50:00
2023-01-05 19:47:00 | 2023-01-06 18:42:00
2023-01-05 20:55:00 | 2023-01-06 10:38:00
2023-01-05 21:17:00 | 2023-01-06 11:16:00
2023-01-06 10:19:00 | 2023-01-06 10:19:00
2023-01-06 14:27:00 | 2023-01-09 13:36:00
2023-01-06 14:40:00 | 2023-01-11 17:56:00
2023-01-06 15:52:00 | 2023-01-11 20:33:00
2023-01-06 16:12:00 | 2023-01-11 18:24:00
2023-01-06 17:14:00 | 2023-01-06 17:14:00
2023-01-06 21:06:00 | 2023-01-18 21:33:00
2023-01-06 23:27:00 | 2023-01-07 19:14:00
2023-01-06 23:28:00 | 2023-01-09 13:39:00
2023-01-06 23:52:00 | 2023-01-12 19:16:00
2023-01-07 01:10:00 | 2023-01-18 17:22:00
2023-01-07 02:53:00 | 2023-01-20 10:58:00
2023-01-07 13:05:00 | 2023-01-19 15:25:00
2023-01-07 14:52:00 | 2023-01-09 17:15:00
2023-01-07 17:54:00 | 2023-01-17 21:53:00
2023-01-08 09:51:00 | 2023-01-09 17:29:00
2023-01-09 11:14:00 | 2023-01-09 18:46:00
2023-01-09 12:33:00 | 2023-01-09 12:35:00
2023-01-09 14:38:00 | 2023-01-09 18:01:00
2023-01-09 16:04:00 | 2023-01-09 17:50:00
2023-01-09 17:03:00 | 2023-01-09 18:32:00
2023-01-09 17:25:00 | 2023-01-09 17:34:00
2023-01-09 20:21:00 | 2023-01-11 18:55:00
2023-01-09 22:39:00 | 2023-01-10 10:53:00
2023-01-09 22:44:00 | 2023-01-11 22:05:00
2023-01-09 23:54:00 | 2023-01-10 16:25:00
2023-01-10 15:02:00 | 2023-01-12 14:01:00
2023-01-10 18:43:00 | 2023-01-18 14:08:00
2023-01-10 19:13:00 | 2023-01-17 11:14:00
2023-01-10 20:13:00 | 2023-01-10 20:41:00
2023-01-10 20:22:00 | 2023-01-17 21:46:00
2023-01-10 22:36:00 | 2023-01-13 15:43:00
2023-01-10 23:25:00 | 2023-01-11 17:33:00
2023-01-11 15:59:00 | 2023-01-12 15:28:00
2023-01-11 18:30:00 | 2023-01-11 19:17:00
2023-01-11 20:54:00 | 2023-01-11 21:40:00
2023-01-11 20:59:00 | 2023-01-19 17:56:00
2023-01-11 21:00:00 | 2023-01-11 21:49:00
2023-01-11 23:44:00 | 2023-01-12 00:53:00
2023-01-11 23:55:00 | 2023-01-12 14:59:00
2023-01-12 00:32:00 | 2023-01-19 22:07:00
2023-01-12 13:51:00 | 2023-01-20 17:21:00
2023-01-12 19:19:00 | 2023-01-13 15:54:00
2023-01-13 13:48:00 | 2023-01-17 10:21:00
2023-01-13 14:42:00 | 2023-01-13 15:08:00
2023-01-13 16:39:00 | 2023-01-17 10:37:00
2023-01-13 17:05:00 | 2023-01-13 17:11:00
2023-01-13 17:26:00 | 2023-01-13 17:40:00
2023-01-13 19:15:00 | 2023-01-13 23:04:00
2023-01-13 19:52:00 | 2023-01-19 13:45:00
2023-01-13 21:30:00 | 2023-01-17 10:49:00
2023-01-13 22:41:00 | 2023-01-17 11:00:00
2023-01-16 16:00:00 | 2023-01-23 13:45:00
2023-01-16 19:46:00 | 2023-01-17 11:13:00
2023-01-16 21:22:00 | 2023-01-17 11:11:00
2023-01-16 21:23:00 | 2023-01-20 10:06:00
2023-01-17 01:08:00 | 2023-01-17 11:25:00
2023-01-17 07:19:00 | 2023-01-17 11:36:00
2023-01-17 11:33:00 | 2023-01-17 11:46:00
2023-01-17 16:05:00 | 2023-01-17 17:49:00
2023-01-17 19:15:00 | 2023-01-17 21:09:00
2023-01-17 22:25:00 | 2023-01-18 18:53:00
2023-01-17 22:37:00 | 2023-01-18 20:03:00
2023-01-18 02:42:00 | 2023-01-18 12:55:00
2023-01-18 13:28:00 | 2023-01-18 14:18:00
2023-01-18 19:53:00 | 2023-01-27 14:27:00
2023-01-18 20:41:00 | 2023-01-18 20:51:00
2023-01-18 21:15:00 | 2023-01-18 21:24:00
2023-01-18 21:30:00 | 2023-01-18 22:04:00
2023-01-19 00:49:00 | 2023-01-23 22:08:00
2023-01-19 01:19:00 | 2023-01-19 13:19:00
2023-01-19 01:31:00 | 2023-01-19 13:17:00
2023-01-19 08:35:00 | 2023-01-23 22:09:00
2023-01-19 20:41:00 | 2023-01-20 10:09:00
2023-01-19 21:15:00 | 2023-01-20 10:25:00
2023-01-19 21:23:00 | 2023-01-19 21:42:00
2023-01-20 01:15:00 | 2023-01-20 18:35:00
2023-01-20 19:55:00 | 2023-01-20 20:30:00
2023-01-20 21:42:00 | 2023-01-20 22:01:00
2023-01-21 00:03:00 | 2023-01-23 14:03:00
2023-01-23 18:29:00 | 2023-01-23 18:38:00
2023-01-23 18:48:00 | 2023-02-01 14:56:00
2023-01-23 19:50:00 | 2023-01-24 15:57:00
2023-01-23 20:38:00 | 2023-01-24 19:56:00
2023-01-23 21:35:00 | 2023-01-23 21:53:00
2023-01-23 22:19:00 | 2023-01-24 10:17:00
2023-01-24 01:31:00 | 2023-01-24 21:59:00
2023-01-24 10:37:00 | 2023-01-24 11:45:00
2023-01-24 19:28:00 | 2023-01-24 20:46:00
2023-01-24 19:35:00 | 2023-01-24 19:54:00
2023-01-24 23:03:00 | 2023-01-25 15:42:00
2023-01-25 13:53:00 | 2023-01-25 14:23:00
2023-01-25 20:01:00 | 2023-01-27 11:28:00
2023-01-25 20:24:00 | 2023-01-27 18:09:00
2023-01-25 20:51:00 | 2023-01-25 22:19:00
2023-01-25 23:18:00 | 2023-01-27 20:28:00
2023-01-26 00:53:00 | 2023-01-27 09:47:00
2023-01-26 15:57:00 | 2023-01-30 17:56:00
2023-01-26 23:08:00 | 2023-02-01 14:57:00
2023-01-26 23:39:00 | 2023-01-30 21:16:00
2023-01-27 15:00:00 | 2023-01-27 16:45:00
2023-01-27 15:04:00 | 2023-01-27 15:10:00
2023-01-28 00:51:00 | 2023-01-31 15:28:00
2023-01-28 04:03:00 | 2023-01-30 17:10:00
2023-01-29 19:51:00 | 2023-01-30 12:25:00
2023-01-30 06:38:00 | 2023-02-07 17:32:00
2023-01-30 17:36:00 | 2023-01-30 21:45:00
2023-01-30 19:07:00 | 2023-01-30 21:38:00
2023-01-30 20:05:00 | 2023-01-30 21:34:00
2023-01-30 21:41:00 | 2023-02-07 16:18:00
2023-01-31 01:32:00 | 2023-01-31 17:37:00
2023-01-31 05:30:00 | 2023-01-31 15:06:00
2023-01-31 10:10:00 | 2023-01-31 10:33:00
2023-01-31 12:46:00 | 2023-01-31 13:35:00
2023-01-31 13:02:00 | 2023-02-01 10:20:00
2023-01-31 13:52:00 | 2023-01-31 15:40:00
2023-01-31 14:17:00 | 2023-01-31 15:26:00
2023-01-31 15:01:00 | 2023-01-31 15:56:00
2023-01-31 23:15:00 | 2023-02-01 10:11:00