Vlookup using TIMEVALUE

Bugas

New member
Joined
Sep 4, 2014
Messages
2
Reaction score
0
Points
0
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.

Is there a way for you to help me?

Thanks :)

ABCD
1Employee nameStart TimeEnd TimeIdentification
2Alan04:0006:00Circadian
3Bruce05: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
 
Why are Kalvin, Lenny & Mark Circadian and not Night?
 
Bugas,

Please provide links to other forums where you have posted the same question. Please read this to find out why we (and all other forums) ask this.
 
Because they work between 02:00 and 06:00 (included)

Well I am out of trying to help with this, I don't bother with cross-posted posts, but I would just say that they are also Night, and Bruce is also Day.
 
Back
Top