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.
Kalvin, Lenny & Mark are Circadian and not Night because they work between 02:00 and 06:00 (included)
Is there a way for you to help me?
I already have a formula for D2, that is all OK. However in D16 I get Day and should be Circadian.
Thanks![Smile :) :)](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
<tbody>
</tbody>
D2
=IF(OR(B2=$B$18,B2=$C$18,C2=$B$18,C2=$C$18,AND(B2<$C$18,B2>$B$18),AND(B2<$B$18,C2>$C$18),AND(B2>C2,AND(B2>$B$18,C2>$C$18))),$A$18,IF(OR(B2=$B$19,B2=$C$19,C2=$B$19,C2=$C$19),$A$19,IF(OR(B2=$B$20,B2=$C$20,C2=$B$20,C2=$C$20),$A$20,IF(OR(B 2=$B$21,B2=$C$21,C2=$B$21,C2=$C$21),$A$21,IF(OR(AND(B2<$C$19,B2>$B$19),AND(B2>C2,AND(B2>$B$19,C2>$C$19))),$A$19,IF(OR(AND(B2<$C$20,B2>$B$20)),$A$20,IF(OR(AND(B2<$C$21,B2>$B$21)),$A$21,"")))))))
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.
Kalvin, Lenny & Mark are Circadian and not Night because they work between 02:00 and 06:00 (included)
Is there a way for you to help me?
I already have a formula for D2, that is all OK. However in D16 I get Day and should be Circadian.
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 | Oscar | 21:10 | 02:35 | Circadian |
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 | |
<tbody>
</tbody>
D2
=IF(OR(B2=$B$18,B2=$C$18,C2=$B$18,C2=$C$18,AND(B2<$C$18,B2>$B$18),AND(B2<$B$18,C2>$C$18),AND(B2>C2,AND(B2>$B$18,C2>$C$18))),$A$18,IF(OR(B2=$B$19,B2=$C$19,C2=$B$19,C2=$C$19),$A$19,IF(OR(B2=$B$20,B2=$C$20,C2=$B$20,C2=$C$20),$A$20,IF(OR(B 2=$B$21,B2=$C$21,C2=$B$21,C2=$C$21),$A$21,IF(OR(AND(B2<$C$19,B2>$B$19),AND(B2>C2,AND(B2>$B$19,C2>$C$19))),$A$19,IF(OR(AND(B2<$C$20,B2>$B$20)),$A$20,IF(OR(AND(B2<$C$21,B2>$B$21)),$A$21,"")))))))