Vlookup using TIMEVALUE

[Bugas]

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?

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

<tbody>
</tbody>

 

[Latchmaker]

Place in 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(B2=$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,"")))))))

 

[Bugas]

Place in 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(B2=$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,"")))))))

Thanks for your support. That's what I was really looking for