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

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Employee name[/TD]
[TD]Start Time[/TD]
[TD]End Time[/TD]
[TD]Identification[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Alan[/TD]
[TD]04:00[/TD]
[TD]06:00[/TD]
[TD]Circadian[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Bruce
[/TD]
[TD]05:00[/TD]
[TD]09:00[/TD]
[TD]Circadian[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Charlie[/TD]
[TD]06:00[/TD]
[TD]08:00[/TD]
[TD]Circadian[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Danny[/TD]
[TD]06:20[/TD]
[TD]13:20[/TD]
[TD]Night[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Ethan[/TD]
[TD]06:29[/TD]
[TD]15:00[/TD]
[TD]Night[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Freddie[/TD]
[TD]06:30[/TD]
[TD]15:00[/TD]
[TD]Day[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Gene[/TD]
[TD]08:00[/TD]
[TD]16:00[/TD]
[TD]Day[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Hans[/TD]
[TD]17:00[/TD]
[TD]22:59[/TD]
[TD]Day[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Irvin[/TD]
[TD]20:00[/TD]
[TD]23:00[/TD]
[TD]Night[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Jack[/TD]
[TD]21:00[/TD]
[TD]01:59[/TD]
[TD]Night[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Kalvin[/TD]
[TD]23:00[/TD]
[TD]02:00[/TD]
[TD]Circadian[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Lenny[/TD]
[TD]23:30[/TD]
[TD]07:00[/TD]
[TD]Circadian[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Mark[/TD]
[TD]01:00[/TD]
[TD]09:00[/TD]
[TD]Circadian[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Norman[/TD]
[TD]01:00[/TD]
[TD]01:59[/TD]
[TD]Night[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Definitions[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Circadian[/TD]
[TD]02:00[/TD]
[TD]06:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]Night[/TD]
[TD]23:00[/TD]
[TD]01:59[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]Night[/TD]
[TD]06:01[/TD]
[TD]06:29[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]Day[/TD]
[TD]06:30[/TD]
[TD]22:59[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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