Vlookup using TIMEVALUE

Bugas

New Member
Joined
Sep 17, 2012
Messages
42
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 :)

ABCD
1Employee nameStart TimeEnd TimeIdentification
2Alan04:0006:00Circadian
3Bruce
05:0009:00Circadian
4Charlie06:0008:00Circadian
5Danny06:2013:20Night
6Ethan06:2915:00Night
7Freddie06:3015:00Day
8Gene08:0016:00Day
9Hans17:0022:59Day
10Irvin20:0023:00Night
11Jack21:0001:59Night
12Kalvin23:0002:00Circadian
13Lenny23:3007:00Circadian
14Mark01:0009:00Circadian
15Norman01:0001:59Night
16
17Definitions
18Circadian02:0006:00
19Night23:0001:59
20Night06:0106:29
21Day06:3022:59

<tbody>
</tbody>
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Latchmaker

Active Member
Joined
Sep 30, 2005
Messages
308
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

New Member
Joined
Sep 17, 2012
Messages
42
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,119
Messages
5,640,219
Members
417,131
Latest member
Seanr19871

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top