+ Reply to Thread
Results 1 to 6 of 6

Trying to match dates with clock in times

Hybrid View

  1. #1
    Registered User
    Join Date
    01-05-2016
    Location
    Richmond, VA
    MS-Off Ver
    MS365 V2406
    Posts
    29

    Trying to match dates with clock in times

    I am trying to find all the clock in/clock out data for an employee, "Joe". I need to have all his clock in/out information transferred to another sheet to make it easier to read. I am trying to make it so that if column B from the Joe Tracker sheet matches a date on the Attendance sheet, it would transfer the clock in and clock out times from the attendance sheet into the Tracker sheet. The "extra" problem is that the attendance sheet may have multiple entries for the same date due to breaks taken, but not all of them will have it, and some will have more than 1 break notated.

    Lines 3 and 4 on the tracker sheet have been filled in manually with the information, note that the break time for lunch (it is always from 12:30 to 13:00, but other times can be noted) was not clocked out by the employee even though the break was taken, and I entered it manually. 04/19/2024 has also been filled in since it was a 2 break day and has 3 lines on the Attendance sheet.
    Attached Files Attached Files

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,973

    Re: Trying to match dates with clock in times

    Something like attached?
    For IN Columns.
    Formula: copy to clipboard
    =IFERROR(INDEX(Attendance!$C$2:$C$126,AGGREGATE(15,6,(ROW($2:$126)-1)/(Attendance!$A$2:$A$126=$B3),ROUNDUP(COLUMNS($A:A)/2,0))),"")

    For OUT Columns.
    Formula: copy to clipboard
    =IFERROR(INDEX(Attendance!$E$2:$E$126,AGGREGATE(15,6,(ROW($2:$126)-1)/(Attendance!$A$2:$A$126=$B3),ROUNDUP(COLUMNS($A:B)/2,0))),"")


    Though personally, I prefer to use PQ for this sort of transformation.
    Attached Files Attached Files
    "Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something."
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    01-05-2016
    Location
    Richmond, VA
    MS-Off Ver
    MS365 V2406
    Posts
    29

    Re: Trying to match dates with clock in times

    I don't want the times to round, I need to know the actual times he clocked in or out.

    PQ?

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,973

    Re: Trying to match dates with clock in times

    ? I'm not rounding times. I'm referencing your Adjusted columns. Just change the reference columns for INDEX's first argument to change it to whatever column you want.

    Power Query.

  5. #5
    Registered User
    Join Date
    01-05-2016
    Location
    Richmond, VA
    MS-Off Ver
    MS365 V2406
    Posts
    29

    Re: Trying to match dates with clock in times

    Got it to work that way, next issue is how do i calculate for the days there are 2 or 3 breaks.

  6. #6
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2019
    Posts
    4,542

    Re: Trying to match dates with clock in times

    ActClockInTime
    =IFERROR(AGGREGATE(15,3,Table1[ActClockInTime]/(Table1[TicketDate]=$B3),COUNTIF($C$2:C$2,C$2)),"")

    ActClockOutTime
    =IFERROR(AGGREGATE(15,3,Table1[ActClockOutTime]/(Table1[TicketDate]=$B3),COUNTIF($C$2:D$2,D$2)),"")

+ 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. How to match dates and times to stock price for 2 assets?
    By Rahul2304 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-19-2021, 11:40 AM
  2. [SOLVED] Consolidating 2 data sets into a table to show a difference in clock in/clock out times
    By Buzz1126 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-24-2019, 10:09 AM
  3. [SOLVED] Convert times to 24 hour clock
    By hazzaska in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-06-2018, 08:58 AM
  4. [SOLVED] Adjusted Clock Times
    By Billy Spivy in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-16-2018, 03:42 PM
  5. IF dates match, and times are consecutive, add a value.
    By jm1g in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 07-28-2017, 07:46 AM
  6. Alarm Clock multiple times
    By ballz in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-04-2011, 04:48 PM
  7. Time (clock) times a number
    By saadeet in forum Excel General
    Replies: 4
    Last Post: 04-02-2009, 11:26 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