# 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?

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>

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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,"")))))))

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

Replies
1
Views
101
Replies
0
Views
201
Replies
1
Views
404
Replies
6
Views
170
Replies
1
Views
160

1,217,454
Messages
6,136,745
Members
450,025
Latest member
Beginner52

### 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