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

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

 

[oldbrewer]

is this correct

start table
00:00	night
00:30	night
01:00	night
01:30	night
02:00	circ
02:30	circ
03:00	circ
03:30	circ
04:00	circ
04:30	circ
05:00	circ
05:30	circ
06:00	circ
06:30	day
07:00	day
07:30	day
08:00	day
08:30	day
09:00	day
09:30	day
10:00	day
10:30	day
11:00	day
11:30	day
12:00	day
12:30	day
13:00	day
13:30	day
14:00	day
14:30	day
15:00	day
15:30	day
16:00	day
16:30	day
17:00	day
17:30	day
18:00	day
18:30	day
19:00	day
19:30	day
20:00	day
20:30	day
21:00	day
21:30	day
22:00	day
22:30	day
22:59	day
23:30	night
23:59	night

<colgroup><col span="2"></colgroup><tbody>
</tbody>

 

[Bugas]

Yes it is ok

is this correct

start table
00:00	night
00:30	night
01:00	night
01:30	night
02:00	circ
02:30	circ
03:00	circ
03:30	circ
04:00	circ
04:30	circ
05:00	circ
05:30	circ
06:00	circ
06:30	day
07:00	day
07:30	day
08:00	day
08:30	day
09:00	day
09:30	day
10:00	day
10:30	day
11:00	day
11:30	day
12:00	day
12:30	day
13:00	day
13:30	day
14:00	day
14:30	day
15:00	day
15:30	day
16:00	day
16:30	day
17:00	day
17:30	day
18:00	day
18:30	day
19:00	day
19:30	day
20:00	day
20:30	day
21:00	day
21:30	day
22:00	day
22:30	day
22:59	day
23:30	night
23:59	night

<tbody>
</tbody>

 

[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