+ Reply to Thread
Results 1 to 2 of 2

Time Punch Contact Tracing

  1. #1
    Registered User
    Join Date
    Dallas, USA
    MS-Off Ver

    Time Punch Contact Tracing

    Hi Team,

    I have an output (attached) which shows the time punches of fictional employees.
    In column J through L, there are a times/dates which are identified as compromised for one reason or another.

    I want to identify and filter to ONLY the times and dates for employees in columns A through E that overlap with the ones in J through L.

    I have tried everything and the only ways I have found are incredibly manual.

    Does anyone have a way to do this quickly and/or efficiently?
    Attached Files Attached Files
    Last edited by TSACov; 10-20-2020 at 11:47 AM.
    - TSACov

  2. #2
    Forum Guru
    Join Date
    MS-Off Ver
    Excel 2010/2019

    Re: Time Punch Contact Tracing

    Perhaps the following will help:
    1. Combine start date and time (column F) using: =IF(E2="","",SUM(C2:D2))
    2. Combine end date and time (column G) using: =IF(E2="","",IF(INT(E2)=0,SUM(C2,E2),E2))
    Note that similar formulas are used in columns M:N
    3. Populate a 'filter column' (column H) using: =SUMPRODUCT(((M$1:M$6<=F2)*(N$1:N$6>=F2))+((M$1:M$6<=G2)*(N$1:N$6>=G2)))
    To see the employees that have start or end date/times that overlap the compromised date/times deselect the zeros and blanks in the filter of cell H1.
    Note that I removed the merged cells and filled in the blanks in column A so that the employee name would be visible when the filter is applied.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. [SOLVED] Need help with Punch in and Punch out clock system please.
    By Grassman in forum Excel Programming / VBA / Macros
    Replies: 74
    Last Post: 02-27-2020, 02:16 AM
  2. Time calculator returning negative result until punch-out is recorded?
    By taylorsm in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-18-2018, 06:39 AM
  3. Punch in Time clock problem
    By giverny in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-12-2018, 06:43 AM
  4. employee time punch
    By skhari in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-16-2015, 01:19 PM
  5. Add, retrieve and save- time punch
    By skhari in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-08-2015, 11:45 PM
  6. Clock in multiple punch in/punch out times Formula
    By Schnizzle74 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-24-2014, 06:15 PM
  7. [SOLVED] Transpose the Punch Time
    By skandkamat in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-30-2013, 01:12 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1