+ Reply to Thread
Results 1 to 2 of 2

Check whether time falls between certain time interval

  1. #1
    Registered User
    Join Date
    10-07-2019
    Location
    Belgium
    MS-Off Ver
    2016
    Posts
    2

    Lightbulb Check whether time falls between certain time interval

    I am currently trying to measure the productivity of blue collars working in my firm. They have to register in a certain tool what they are doing at each moment of the day. For example, as you can see in my file, Jef says he is doing task A from 4.01am until 4.09am

    Besides what they register in the file, they also generate a certain activity in another program. This is the output you can see in the second table.

    I want to check whether these to values correspond, meaning that I want to check that these guys are really doing what they say they are doing. the system output should match what these people say they're doing at all times. these two tables should match.

    The outcome should be what I have highlighted in red. These values are wrong so they should be red.
    Jef says he is doing task A from 4.01am-4.09am, but in reality he did task B at 4.06am, which is why this is highlighted in red.
    Jef says he is doing task A from 6.44am - 12.30pm, but in reality he executed task C at 7.45am --> red
    Dan says he is doing task B from 9.07am - 10.28 am, but in reality he did task A at 9.40am --> red

    The other should be green because they are correct. Example:
    Dan says he is doing task F from 10.28am - 14.41pm, which matches the value in the second table --> green.

    Can anyone please help me with this?
    Any help is highly appreciated!
    Attached Files Attached Files
    Last edited by 6StringJazzer; 05-08-2020 at 10:51 AM.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Check whether time falls between certain time interval

    Format the cells in column G as red. This is the default.

    Add a conditional formatting rule for cells in G starting in row 3:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    This will change the cell to green when the task in column G matches what they reported working on at that time.

    Solution attached.
    Attached Files Attached Files
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Check if the in and out time falls between a specific time interval
    By srivibish in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-27-2020, 03:18 AM
  2. Replies: 1
    Last Post: 07-23-2019, 11:09 AM
  3. Replies: 1
    Last Post: 07-23-2019, 05:17 AM
  4. Check if cell is in time interval
    By soli004 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-23-2016, 09:49 AM
  5. COUNTIF time interval falls between set of time ranges
    By eyoonbbj in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-15-2014, 01:44 PM
  6. [SOLVED] SUM data that falls within a time interval
    By shelsx in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-12-2012, 11:17 PM
  7. Replies: 0
    Last Post: 04-23-2012, 10:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1