Results 1 to 5 of 5

Thread: Vlookup using TIMEVALUE

  1. #1

    Vlookup using TIMEVALUE



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

    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

  2. #2
    Why are Kalvin, Lenny & Mark Circadian and not Night?

  3. #3
    Quote Originally Posted by Bob Phillips View Post
    Why are Kalvin, Lenny & Mark Circadian and not Night?
    Because they work between 02:00 and 06:00 (included)

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,499
    Articles
    0
    Excel Version
    Excel 2016
    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.


  5. #5
    Quote Originally Posted by Bugas View Post
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •