# Vlookup using TIMEVALUE

#### Bugas

##### New Member
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:

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

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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>

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>

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

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

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

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

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

Replies
1
Views
202
Replies
0
Views
337
Replies
11
Views
328
Replies
1
Views
534
Replies
2
Views
193

1,221,006
Messages
6,157,344
Members
451,417
Latest member
Ilu

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back