Spreadsheet: 878433d1725517296-calculate-working-hours-working-hours
Sheet: Data

00:30:00 |  | 2024-08-01 00:00:00 |  | 2024-08-02 00:00:00 |  | 2024-08-03 00:00:00 |  | 2024-08-04 00:00:00 |  | 2024-08-05 00:00:00 |  | 2024-08-06 00:00:00 |  | 2024-08-07 00:00:00 |  | 2024-08-08 00:00:00 |  | 2024-08-09 00:00:00 |  | 2024-08-10 00:00:00 |  | 2024-08-11 00:00:00 |  | 2024-08-12 00:00:00 |  | 2024-08-13 00:00:00 |  | 2024-08-14 00:00:00 |  | 2024-08-15 00:00:00 |  | 2024-08-16 00:00:00 |  | 2024-08-17 00:00:00 |  | 2024-08-18 00:00:00 |  | 2024-08-19 00:00:00 |  | 2024-08-20 00:00:00 |  | 2024-08-21 00:00:00 |  | 2024-08-22 00:00:00 |  | 2024-08-23 00:00:00 |  | 2024-08-24 00:00:00 |  | 2024-08-25 00:00:00 |  | 2024-08-26 00:00:00 |  | 2024-08-27 00:00:00 |  | 2024-08-28 00:00:00 |  | 2024-08-29 00:00:00 |  | 2024-08-30 00:00:00 |  | 2024-08-31 00:00:00 |  | 1900-01-01 00:00:00
Working Hours | Name |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Working Hours
08:47:00 | A | 07:49:50 | 16:14:52 | 07:51:01 | 16:19:47 | 07:40:53 | 16:23:47 | 07:54:25 | 16:22:34 | 07:50:02 | 16:16:07 | W | W | W+ | 08:21:16 | 07:52:18 | 16:09:35 | 07:56:01 | 16:14:04 | 07:56:55 | 16:25:36 | 07:52:55 | 16:15:04 | 00:00:00 | 00:00:00 | W | W | W+ | 08:21:08 | 07:57:07 | 16:11:37 | 07:51:16 | 16:19:37 | 07:50:34 | 16:18:40 | 08:00:37 | 16:20:53 | 07:54:20 | 16:22:58 | W | W | W+ | 08:22:22 | 07:46:57 | 16:19:01 | 07:49:16 | 16:15:23 | 07:58:51 | 16:19:39 | 07:50:57 | 16:13:58 | 07:52:53 | 16:13:42 | W | W | W+ | 08:28:50 | 07:50:00 | 16:15:47 | 07:48:30 | 16:19:17 | 07:53:41 | 16:26:27
08:05:00 | B | 23:28:18 | 08:25:54 | 23:26:04 | 08:25:13 | 00:00:00 | 00:00:00 | 00:00:00 | 00:00:00 | 15:55:55 | 23:56:14 | 15:59:57 | 23:55:45 | 15:53:58 | 23:53:43 | 15:58:50 | 23:52:31 | 15:58:24 | 23:54:26 | 00:00:00 | 00:00:00 | 07:56:46 | 16:24:22 | 07:56:49 | 20:25:45 | 07:56:47 | 20:25:10 | 07:56:10 | 20:26:07 | 00:00:00 | 00:00:00 | 00:00:00 | 00:00:00 | 19:57:20 | 08:24:07 | 23:29:13 | 08:24:20 | 23:24:01 | 08:20:06 | 23:25:08 | 08:25:27 | 23:28:28 | 08:25:48 | 00:00:00 | 00:00:00 | 00:00:00 | 00:00:00 | 00:00:00 | 00:00:00 | 16:03:08 | 23:53:32 | 15:58:51 | 23:54:45 | 16:00:50 | 23:54:54 | 16:01:51 | 23:51:53 | 00:00:00 | 00:00:00 | 08:01:44 | 16:23:33 | 07:54:49 | 16:22:18
08:47:00 | C | 07:49:47 | 16:25:47 | 00:00:00 | 00:00:00 | 07:41:13 | 16:23:50 | 07:55:10 | 20:23:26 | W+ | 08:34:28 | 07:47:26 | 16:21:10 | W | W | 00:00:00 | 00:00:00 | 15:50:11 | 23:55:23 | 15:55:07 | 23:50:22 | 19:54:50 | 08:24:44 | W+ | 12:30:51 | 19:52:05 | 08:24:44 | W+ | 08:55:32 | 00:00:00 | 00:00:00 | 15:52:00 | 23:56:10 | 15:58:12 | 23:57:09 | 00:00:00 | 00:00:00 | W+ | 07:51:09 | 15:59:26 | 23:54:57 | W | W | 07:47:20 | 16:21:23 | 07:49:48 | 20:25:00 | 07:58:52 | 16:22:03 | 07:51:38 | 16:22:47 | W+ | 12:34:51 | 07:53:24 | 16:24:03 | W | W | 00:00:00 | 00:00:00 | 07:48:33 | 16:21:57 | 07:53:08 | 16:20:19
08:47:00 | D | 15:46:36 | 23:53:47 | 07:51:35 | 16:23:23 | W+ | 08:08:04 | 15:47:06 | 23:56:10 | 07:42:22 | 20:23:01 | W | W | 15:59:00 | 23:53:33 | 15:48:09 | 23:54:04 | 07:59:07 | 20:25:19 | W+ | 08:34:58 | 07:42:51 | 16:24:26 | 07:46:34 | 20:25:58 | W | W | 07:40:15 | 16:24:23 | 07:47:10 | 20:27:24 | 08:01:05 | 16:23:40 | W+ | 08:45:53 | 07:39:39 | 16:22:20 | 07:44:27 | 20:24:01 | W+ | 08:40:00 | 00:00:00 | 00:00:00 | 15:46:17 | 23:56:23 | 19:56:18 | 08:24:18 | W+ | 12:28:12 | 19:49:55 | 08:27:25 | 19:55:10 | 08:25:16 | W | W | 15:51:27 | 23:56:11 | 00:00:00 | 00:00:00 | 15:43:40 | 23:53:59 | W+ | 08:03:42
08:05:00 | E | 23:30:28 | 08:23:53 | 23:27:22 | 08:24:56 | 23:29:29 | 08:22:24 | 00:00:00 | 00:00:00 | 15:56:51 | 23:55:47 | 16:02:09 | 23:53:54 | 15:58:59 | 23:52:10 | 15:58:17 | 23:53:53 | 15:58:42 | 23:53:23 | 00:00:00 | 00:00:00 | 07:36:32 | 20:25:46 | 07:44:33 | 16:21:41 | 07:45:27 | 20:21:31 | 07:53:55 | 16:19:00 | 00:00:00 | 00:00:00 | 00:00:00 | 00:00:00 | 23:24:36 | 08:23:27 | 20:04:02 | 08:22:59 | 23:27:04 | 08:23:28 | 23:28:48 | 08:26:52 | 23:27:04 | 08:21:28 | 00:00:00 | 00:00:00 | 00:00:00 | 00:00:00 | 07:30:23 | 20:26:32 | 20:00:32 | 08:26:48 | 00:00:00 | 00:00:00 | 15:54:38 | 23:57:12 | 15:59:53 | 23:55:02 | 00:00:00 | 00:00:00 | 07:50:35 | 20:25:44 | 07:38:15 | 16:20:15
08:05:00 | F | 07:36:28 | 16:21:31 | 07:42:54 | 16:23:01 | 07:45:02 | 16:24:11 | 07:53:51 | 18:56:13 | 07:53:13 | 16:23:43 | 00:00:00 | 00:00:00 | 23:28:21 | 08:23:43 | 20:01:43 | 08:25:50 | 23:26:52 | 08:23:16 | 23:30:21 | 08:22:21 | 20:01:22 | 08:23:02 | 00:00:00 | 00:00:00 | 00:00:00 | 00:00:00 | 07:53:28 | 16:24:30 | 16:00:18 | 23:54:03 | 16:01:26 | 23:55:12 | 15:59:23 | 23:54:45 | 15:59:44 | 23:52:43 | 00:00:00 | 00:00:00 | 07:33:59 | 16:22:38 | 07:34:51 | 16:24:06 | 07:53:16 | 16:20:48 | 00:00:00 | 00:00:00 | 00:00:00 | 00:00:00 | 00:00:00 | 00:00:00 | 20:03:00 | 08:22:47 | 20:01:56 | 08:25:05 | 23:27:17 | 08:26:07 | 20:04:37 | 08:23:45 | 20:02:24 | 08:26:55 | 00:00:00 | 00:00:00
08:05:00 | G | 00:00:00 | 00:00:00 | 23:15:59 | 08:23:49 | 00:00:00 | 00:00:00 | 00:00:00 | 00:00:00 | 23:25:15 | 08:22:19 | 23:21:14 | 08:19:31 | 00:00:00 | 00:00:00 | 00:00:00 | 00:00:00 | 07:50:06 | 16:24:45 | 15:55:05 | 23:49:02 | 15:49:29 | 23:48:20 | 07:40:08 | 16:20:53 | 07:43:59 | 16:16:21 | 00:00:00 | 00:00:00 | 07:49:15 | 16:19:49 | 07:53:26 | 16:20:08 | 07:43:13 | 20:15:59 | 07:46:30 | 16:20:56 | 07:45:25 | 16:13:57 | 00:00:00 | 00:00:00 | 23:21:32 | 08:21:25 | 23:27:34 | 08:25:30 | 23:27:35 | 08:24:34 | 23:19:38 | 08:26:39 | 00:00:00 | 00:00:00 | 00:00:00 | 00:00:00 | 00:00:00 | 00:00:00 | 07:38:47 | 16:14:28 | 07:44:51 | 16:15:32 | 15:56:33 | 23:51:28 | 15:49:17 | 23:50:39
08:05:00 | H | 07:44:07 | 16:21:21 | 07:46:29 | 16:23:30 | 07:38:26 | 16:20:37 | 07:39:15 | 16:24:03 | 07:41:51 | 16:22:08 | 00:00:00 | 00:00:00 | 23:28:13 | 08:23:43 | 23:35:49 | 08:23:00 | 23:27:06 | 08:24:15 | 20:03:44 | 08:22:14 | 23:26:58 | 08:22:24 | 00:00:00 | 00:00:00 | 00:00:00 | 00:00:00 | 20:00:44 | 08:21:12 | 20:03:51 | 08:23:21 | 20:00:47 | 08:20:28 | 00:00:00 | 00:00:00 | 15:59:56 | 23:48:57 | 00:00:00 | 00:00:00 | 07:40:24 | 16:17:48 | 07:40:59 | 16:24:36 | 07:41:09 | 16:21:34 | 07:47:53 | 18:57:05 | 07:39:59 | 18:56:30 | 00:00:00 | 00:00:00 | 23:29:28 | 08:22:58 | 23:26:24 | 08:22:55 | 23:27:13 | 08:24:40 | 23:27:29 | 08:23:52 | 23:26:52 | 08:21:28 | 00:00:00 | 00:00:00
08:47:00 | I | 07:42:19 | 16:19:53 | 07:52:42 | 16:23:05 | W+ | 08:32:24 | W | W | 19:52:46 | 08:22:26 | 15:49:02 | 23:45:24 | 19:55:56 | 08:19:56 | 19:37:19 | 08:22:53 | 19:27:12 | 08:19:39 | W+ | 09:09:32 | W | W | 07:46:46 | 16:20:56 | 07:44:43 | 20:21:31 | 07:49:43 | 16:24:23 | 08:00:16 | 16:24:06 | 07:51:23 | 16:23:34 | W+ | 08:43:53 | W+ | 12:28:16 | 00:00:00 | 00:00:00 | 07:41:33 | 20:21:28 | 15:43:21 | 23:41:55 | 15:49:08 | 23:45:02 | 00:00:00 | 00:00:00 | W+ | 07:55:45 | W | W | 07:45:46 | 16:22:50 | 07:42:54 | 20:21:14 | 07:42:47 | 20:23:20 | 07:45:25 | 20:24:26 | 07:51:00 | 16:21:52 | W+ | 08:30:14
08:47:00 | J | 07:56:08 | 16:26:28 | 08:03:11 | 16:26:36 | W | W | 07:57:27 | 16:25:22 | 08:00:43 | 16:19:49 | W+ | 08:23:15 | 08:01:04 | 16:25:17 | 07:59:35 | 16:22:43 | 07:57:01 | 16:24:03 | W | W | 08:04:20 | 16:24:09 | 07:58:31 | 16:24:59 | W+ | 08:25:49 | 08:05:27 | 16:12:46 | 00:00:00 | 00:00:00 | 08:01:02 | 16:25:20 | W | W | 08:05:17 | 16:19:55 | 08:04:28 | 16:24:07 | W+ | 08:27:10 | 08:04:00 | 16:25:16 | 08:03:38 | 16:22:43 | 07:58:51 | 16:24:18 | W | W | 07:57:46 | 16:24:19 | 07:57:45 | 16:25:22 | W+ | 08:30:11 | 07:57:33 | 16:26:20 | 07:56:44 | 11:29:00 | 08:00:11 | 16:24:56 | W | W
 |  |  | I want a formula instead of the big one in column "BO",it calculates working hours for each employee for the whole month:
 |  | * | "in" and "out" should be numbers to caculate working hours,so ignore any date that contain letters in both "in" and "out"
 |  | * | if "in" equals "W+" or "H+" ,"out" will contain working hours for that day