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?

I already have a formula for D2, that is all OK. However in D16 I get Day and should be Circadian.


Thanks




[TABLE="class: cms_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]Oscar[/TD]
[TD]21:10[/TD]
[TD]02:35[/TD]
[TD]Circadian[/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]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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,"")))))))

 

[oldbrewer]

is this correct

[TABLE="width: 128"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]start table[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]00:00[/TD]
[TD]night[/TD]
[/TR]
[TR]
[TD="align: right"]00:30[/TD]
[TD]night[/TD]
[/TR]
[TR]
[TD="align: right"]01:00[/TD]
[TD]night[/TD]
[/TR]
[TR]
[TD="align: right"]01:30[/TD]
[TD]night[/TD]
[/TR]
[TR]
[TD="align: right"]02:00[/TD]
[TD]circ[/TD]
[/TR]
[TR]
[TD="align: right"]02:30[/TD]
[TD]circ[/TD]
[/TR]
[TR]
[TD="align: right"]03:00[/TD]
[TD]circ[/TD]
[/TR]
[TR]
[TD="align: right"]03:30[/TD]
[TD]circ[/TD]
[/TR]
[TR]
[TD="align: right"]04:00[/TD]
[TD]circ[/TD]
[/TR]
[TR]
[TD="align: right"]04:30[/TD]
[TD]circ[/TD]
[/TR]
[TR]
[TD="align: right"]05:00[/TD]
[TD]circ[/TD]
[/TR]
[TR]
[TD="align: right"]05:30[/TD]
[TD]circ[/TD]
[/TR]
[TR]
[TD="align: right"]06:00[/TD]
[TD]circ[/TD]
[/TR]
[TR]
[TD="align: right"]06:30[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]07:00[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]07:30[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]08:00[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]08:30[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]09:00[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]09:30[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]10:00[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]10:30[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]11:00[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]11:30[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]12:00[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]12:30[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]13:00[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]13:30[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]14:00[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]14:30[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]15:00[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]15:30[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]16:00[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]16:30[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]17:00[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]17:30[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]18:00[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]18:30[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]19:00[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]19:30[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]20:00[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]20:30[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]21:00[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]21:30[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]22:00[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]22:30[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]22:59[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]23:30[/TD]
[TD]night[/TD]
[/TR]
[TR]
[TD="align: right"]23:59[/TD]
[TD]night[/TD]
[/TR]
</tbody>[/TABLE]

 

[Bugas]

Yes it is ok

is this correct

[TABLE="width: 128"]
<tbody>[TR]
[TD]start table[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]00:00[/TD]
[TD]night[/TD]
[/TR]
[TR]
[TD="align: right"]00:30[/TD]
[TD]night[/TD]
[/TR]
[TR]
[TD="align: right"]01:00[/TD]
[TD]night[/TD]
[/TR]
[TR]
[TD="align: right"]01:30[/TD]
[TD]night[/TD]
[/TR]
[TR]
[TD="align: right"]02:00[/TD]
[TD]circ[/TD]
[/TR]
[TR]
[TD="align: right"]02:30[/TD]
[TD]circ[/TD]
[/TR]
[TR]
[TD="align: right"]03:00[/TD]
[TD]circ[/TD]
[/TR]
[TR]
[TD="align: right"]03:30[/TD]
[TD]circ[/TD]
[/TR]
[TR]
[TD="align: right"]04:00[/TD]
[TD]circ[/TD]
[/TR]
[TR]
[TD="align: right"]04:30[/TD]
[TD]circ[/TD]
[/TR]
[TR]
[TD="align: right"]05:00[/TD]
[TD]circ[/TD]
[/TR]
[TR]
[TD="align: right"]05:30[/TD]
[TD]circ[/TD]
[/TR]
[TR]
[TD="align: right"]06:00[/TD]
[TD]circ[/TD]
[/TR]
[TR]
[TD="align: right"]06:30[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]07:00[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]07:30[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]08:00[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]08:30[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]09:00[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]09:30[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]10:00[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]10:30[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]11:00[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]11:30[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]12:00[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]12:30[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]13:00[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]13:30[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]14:00[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]14:30[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]15:00[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]15:30[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]16:00[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]16:30[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]17:00[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]17:30[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]18:00[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]18:30[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]19:00[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]19:30[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]20:00[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]20:30[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]21:00[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]21:30[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]22:00[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]22:30[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]22:59[/TD]
[TD]day[/TD]
[/TR]
[TR]
[TD="align: right"]23:30[/TD]
[TD]night[/TD]
[/TR]
[TR]
[TD="align: right"]23:59[/TD]
[TD]night[/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

then just use a lookup table for start time and a similar table for end time - if this is necessary

 

[Bugas]

But how do I use both of them (start time and end time)?

then just use a lookup table for start time and a similar table for end time - if this is necessary

 

[oldbrewer]

you need to think about your definitions - if work started at 7 pm and finished at 7 am would it all be CIRC ?

if work started at 03:00 and finished at 3pm would it all be CIRC

I am thinking of a matrix 0 to 24 down the side for start times and 0 to 24 along the top for finish times then populate each intersection with your definitions

 

[Bugas]

you need to think about your definitions - if work started at 7 pm and finished at 7 am would it all be CIRC ?

if work started at 03:00 and finished at 3pm would it all be CIRC

I am thinking of a matrix 0 to 24 down the side for start times and 0 to 24 along the top for finish times then populate each intersection with your definitions

if work started at 7 pm and finished at 7 am would it all be CIRC - Yes
if work started at 03:00 and finished at 3pm would it all be CIRC - Yes

 

[oldbrewer]

so a single lookup table but lookup both start and finish times - if EITHER is between 02:00 and 06:00 then circ - if circ not returned use another lookup table for the other categories