I'm trying to identify which employees work at day, at night or during circadian time. Without using VBA is there any formula in excel to this?
In this example I have the result I want to get at Identification (Column D).
The conditions are above (A17). The most important is:
1 circadian (02:00 - 06:00)
2 night (23:00 - 06:29)
3 day (6:30 - 22:59).
By definition circadian is part of the night, so it seems a duplicate issue but it is not. Other important issue is that by touching only one minute of the most important, you'll get the Identification. For example - Start working at 6:00 is considered circadian. Start working at 6:29 is considered night. Stop working at 23:00 is considered night. Stop working at 02:00 is considered circadian.
Is there a way for you to help me?
Thanks
In this example I have the result I want to get at Identification (Column D).
The conditions are above (A17). The most important is:
1 circadian (02:00 - 06:00)
2 night (23:00 - 06:29)
3 day (6:30 - 22:59).
By definition circadian is part of the night, so it seems a duplicate issue but it is not. Other important issue is that by touching only one minute of the most important, you'll get the Identification. For example - Start working at 6:00 is considered circadian. Start working at 6:29 is considered night. Stop working at 23:00 is considered night. Stop working at 02:00 is considered circadian.
Is there a way for you to help me?
Thanks
A | B | C | D | |
1 | Employee name | Start Time | End Time | Identification |
2 | Alan | 04:00 | 06:00 | Circadian |
3 | Bruce | 05:00 | 09:00 | Circadian |
4 | Charlie | 06:00 | 08:00 | Circadian |
5 | Danny | 06:20 | 13:20 | Night |
6 | Ethan | 06:29 | 15:00 | Night |
7 | Freddie | 06:30 | 15:00 | Day |
8 | Gene | 08:00 | 16:00 | Day |
9 | Hans | 17:00 | 22:59 | Day |
10 | Irvin | 20:00 | 23:00 | Night |
11 | Jack | 21:00 | 01:59 | Night |
12 | Kalvin | 23:00 | 02:00 | Circadian |
13 | Lenny | 23:30 | 07:00 | Circadian |
14 | Mark | 01:00 | 09:00 | Circadian |
15 | Norman | 01:00 | 01:59 | Night |
16 | ||||
17 | Definitions | |||
18 | Circadian | 02:00 | 06:00 | |
19 | Night | 23:00 | 01:59 | |
20 | Night | 06:01 | 06:29 | |
21 | Day | 06:30 | 22:59 |