Spreadsheet: 748423d1632142549-how-to-determine-whether-a-worked-shift-is-morning-afternoon-or-night-sepmonth
Sheet: Data

ID | NAME | DEPT | DATE | TIME | Missing value | Early | Late |  | Shift | From | To | Working hours |  | => Output | ID | NAME | 2021-09-01 00:00:00 | 2021-09-02 00:00:00 | 2021-09-03 00:00:00 | 2021-09-04 00:00:00 | 2021-09-05 00:00:00 | 2021-09-06 00:00:00 | 2021-09-07 00:00:00 | 2021-09-08 00:00:00 | 2021-09-09 00:00:00 | 2021-09-10 00:00:00 | 2021-09-11 00:00:00 | 2021-09-12 00:00:00 | 2021-09-13 00:00:00 | 2021-09-14 00:00:00 | 2021-09-15 00:00:00 | 2021-09-16 00:00:00 | 2021-09-17 00:00:00 | 2021-09-18 00:00:00 | 2021-09-19 00:00:00 | 2021-09-20 00:00:00 | 2021-09-21 00:00:00 | 2021-09-22 00:00:00 | 2021-09-23 00:00:00 | 2021-09-24 00:00:00 | 2021-09-25 00:00:00 | 2021-09-26 00:00:00 | 2021-09-27 00:00:00 | 2021-09-28 00:00:00 | 2021-09-29 00:00:00 | 2021-09-30 00:00:00
101505007 | TINA | SALES | 2021-09-01 00:00:00 | 07:33:00 |  |  |  |  | M | 06:00:00 | 14:00:00 | 8 |  |  | 101505007 | TINA | AD | AD |  |  | AD |  | AD | AD | AD | AD |  | AD |  | AD |  | AD |  |  | AD
101505007 | TINA | SALES | 2021-09-01 00:00:00 | 17:20:00 |  |  |  |  | A | 14:00:00 | 22:00:00 | 8 |  |  | 601905018 | ANDY | A | A |  | N | N | N | N | N
101505007 | TINA | SALES | 2021-09-02 00:00:00 | 07:57:00 |  |  |  |  | N | 22:00:00 | 06:00:00 | 8 |  |  | 601905020 | STEVEN |  | M | A | N |  |  | A | N |  | A | N |  |  |  |  |  | A | M | A
101505007 | TINA | SALES | 2021-09-02 00:00:00 | 17:14:00 |  |  |  |  | AD | 09:00:00 | 17:00:00 | 8
101505007 | TINA | SALES | 2021-09-05 00:00:00 | 07:52:00
101505007 | TINA | SALES | 2021-09-05 00:00:00 | 17:30:00
101505007 | TINA | SALES | 2021-09-06 00:00:00 | 18:01:00 | Plz check |  |  |  | Condition
101505007 | TINA | SALES | 2021-09-07 00:00:00 | 08:12:00 |  |  |  |  | If missing value, In or out => print "plz check"
101505007 | TINA | SALES | 2021-09-07 00:00:00 | 17:35:00 |  |  |  |  | Due to shift define => check early and late hour
101505007 | TINA | SALES | 2021-09-08 00:00:00 | 07:42:00 |  |  |  |  | Print output: attendance record
101505007 | TINA | SALES | 2021-09-08 00:00:00 | 18:08:00
101505007 | TINA | SALES | 2021-09-09 00:00:00 | 07:49:00
101505007 | TINA | SALES | 2021-09-09 00:00:00 | 18:54:00
101505007 | TINA | SALES | 2021-09-10 00:00:00 | 06:23:00
101505007 | TINA | SALES | 2021-09-10 00:00:00 | 17:02:00
101505007 | TINA | SALES | 2021-09-12 00:00:00 | 07:51:00
101505007 | TINA | SALES | 2021-09-12 00:00:00 | 17:46:00
101505007 | TINA | SALES | 2021-09-13 00:00:00 | 07:49:00 | Plz check
101505007 | TINA | SALES | 2021-09-14 00:00:00 | 07:48:00
101505007 | TINA | SALES | 2021-09-14 00:00:00 | 17:59:00
101505007 | TINA | SALES | 2021-09-15 00:00:00 | 06:40:00 | Plz check
101505007 | TINA | SALES | 2021-09-16 00:00:00 | 07:54:00
101505007 | TINA | SALES | 2021-09-16 00:00:00 | 18:05:00
101505007 | TINA | SALES | 2021-09-19 00:00:00 | 08:37:00
101505007 | TINA | SALES | 2021-09-19 00:00:00 | 17:57:00
601905018 | ANDY | FO | 2021-09-01 00:00:00 | 13:40:00
601905018 | ANDY | FO | 2021-09-01 00:00:00 | 22:05:00
601905018 | ANDY | FO | 2021-09-02 00:00:00 | 13:23:00
601905018 | ANDY | FO | 2021-09-02 00:00:00 | 22:08:00
601905018 | ANDY | FO | 2021-09-04 00:00:00 | 21:11:00
601905018 | ANDY | FO | 2021-09-05 00:00:00 | 06:38:00
601905018 | ANDY | FO | 2021-09-05 00:00:00 | 21:08:00
601905018 | ANDY | FO | 2021-09-06 00:00:00 | 06:22:00
601905018 | ANDY | FO | 2021-09-06 00:00:00 | 21:31:00
601905018 | ANDY | FO | 2021-09-07 00:00:00 | 06:16:00
601905018 | ANDY | FO | 2021-09-07 00:00:00 | 21:31:00
601905018 | ANDY | FO | 2021-09-08 00:00:00 | 06:11:00
601905018 | ANDY | FO | 2021-09-08 00:00:00 | 21:46:00
601905018 | ANDY | FO | 2021-09-09 00:00:00 | 06:23:00
601905020 | STEVEN | SEC | 2021-09-01 00:00:00 | 07:03:00 | Plz check
601905020 | STEVEN | SEC | 2021-09-02 00:00:00 | 05:52:00
601905020 | STEVEN | SEC | 2021-09-02 00:00:00 | 15:45:00
601905020 | STEVEN | SEC | 2021-09-03 00:00:00 | 13:28:00
601905020 | STEVEN | SEC | 2021-09-03 00:00:00 | 22:30:00
601905020 | STEVEN | SEC | 2021-09-04 00:00:00 | 21:54:00
601905020 | STEVEN | SEC | 2021-09-05 00:00:00 | 06:35:00
601905020 | STEVEN | SEC | 2021-09-06 00:00:00 | 14:26:00 | Plz check
601905020 | STEVEN | SEC | 2021-09-07 00:00:00 | 14:07:00
601905020 | STEVEN | SEC | 2021-09-07 00:00:00 | 22:29:00
601905020 | STEVEN | SEC | 2021-09-08 00:00:00 | 21:58:00
601905020 | STEVEN | SEC | 2021-09-09 00:00:00 | 06:28:00
601905020 | STEVEN | SEC | 2021-09-10 00:00:00 | 13:13:00
601905020 | STEVEN | SEC | 2021-09-10 00:00:00 | 22:47:00
601905020 | STEVEN | SEC | 2021-09-11 00:00:00 | 22:34:00
601905020 | STEVEN | SEC | 2021-09-12 00:00:00 | 21:53:00
601905020 | STEVEN | SEC | 2021-09-17 00:00:00 | 13:30:00
601905020 | STEVEN | SEC | 2021-09-17 00:00:00 | 22:38:00
601905020 | STEVEN | SEC | 2021-09-18 00:00:00 | 06:06:00
601905020 | STEVEN | SEC | 2021-09-18 00:00:00 | 17:00:00
601905020 | STEVEN | SEC | 2021-09-19 00:00:00 | 14:20:00
601905020 | STEVEN | SEC | 2021-09-19 00:00:00 | 22:36:00