+ Reply to Thread
Results 1 to 14 of 14

How to Get Earliest Time In and Latest Time Out

  1. #1
    Registered User
    Join Date
    12-15-2012
    Location
    Manila
    MS-Off Ver
    Excel 2003
    Posts
    9

    How to Get Earliest Time In and Latest Time Out

    Hi experts!

    I am trying to solve this for quite some time. I have a workbook with two sheets. On the first sheet it is the raw data. The raw data contains the actual log-ins and log-outs of the agents. On the second sheet is the report. My problem is, there are agents that have multiple log-ins and log-outs during the day. What I wat do is that on my LILO sheet I would only have the earliest log-in time and the latest log-out time of each agent.

    Hope you can help thank you!
    Attached Files Attached Files

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: How to Get Earliest Time In and Latest Time Out

    Try using these array formulas entered with CTRL + SHIFT + ENTER

    On the LILO sheet,
    B3: =MIN(IF(Raw!$A$2:$A$61=A3,Raw!$C$2:$C$61))
    C3: =MAX(IF(Raw!$A$2:$A$61=A3,Raw!$E$2:$E$61))

  3. #3
    Registered User
    Join Date
    08-05-2013
    Location
    USA
    MS-Off Ver
    2010
    Posts
    76

    Re: How to Get Earliest Time In and Latest Time Out

    B3: =MIN(IF(Raw!$D$2:$D$61=$B$1,IF(Raw!$A$2:$A$61=A3,Raw!$C$2:$C$61)))
    C3: =MAX(IF(Raw!$D$2:$D$61=$B$1,IF(Raw!$A$2:$A$61=A3,Raw!$E$2:$E$61)))

    Enter by pressing Ctrl+Shift+Enter

    And if you want it to be blank if they didn't work that day, add ,"" before the last parentheses.
    Last edited by jeffr27; 09-18-2013 at 04:29 PM.

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to Get Earliest Time In and Latest Time Out

    Try these array formulas**:

    Entered in B3:

    =IFERROR(1/(1/MIN(IF(Raw!A$2:A$61=A3,Raw!C$2:C$61))),"")

    Format as h:mm

    Entered in C3:

    =IFERROR(1/(1/MAX(IF(Raw!A$2:A$61=A3,Raw!E$2:E$61))),"")

    Format as h:mm


    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Select B3:C3 and copy down as needed.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Registered User
    Join Date
    12-15-2012
    Location
    Manila
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: How to Get Earliest Time In and Latest Time Out

    Hi!

    Thank you for the reply.

    I am still having problems.

    Consider this:

    Dominic's shift is 22:00-07:00.
    He logs in at 22:00 but accidentally logged out at 23:00, he immediately logs back in and ended his shift at 07:00. When i use the formula, the formula for the log-out reports 23:00 as his log-out and not 07:00.

    Another one:
    Rey's shift is 23:00-08:00
    Here how is the Raw Report Looks like:

    Name ----------------- Date ------------- Log-In -------------LogOut Date --------------- Log-Out
    Rey 9/8/2013 23:00 9/9/2013 01:30
    Rey 9/9/2013 01:30 9/9/2013 08:00
    Rey 9/9/2013 23:00 9/10/2013 08:00

    With the above data when I ran the MIN formula for Sept 9, it will return the value 01:30 and not 23:00.

    Thanks!

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: How to Get Earliest Time In and Latest Time Out

    Now we have a real problem..


    Is it fair to say that the data is sorted?
    The Login time that is Closest to the TOP of the list for each person is the time you're looking for
    The Logout time that is Furthest to the bottom of the list for each person is the time you're looking for

  7. #7
    Registered User
    Join Date
    12-15-2012
    Location
    Manila
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: How to Get Earliest Time In and Latest Time Out

    That is absolutely correct... that is how the program generates its report.

  8. #8
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: How to Get Earliest Time In and Latest Time Out

    So for a person that works past midnight, which Date is considered the Date that person worked?
    The date of Pre or Post Midnight?

  9. #9
    Registered User
    Join Date
    12-15-2012
    Location
    Manila
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: How to Get Earliest Time In and Latest Time Out

    It will be pre-midnight

  10. #10
    Registered User
    Join Date
    08-05-2013
    Location
    USA
    MS-Off Ver
    2010
    Posts
    76

    Re: How to Get Earliest Time In and Latest Time Out

    This is a rather weird way to do it but here's what I've got:

    =MIN(IF(Raw!$B$2:$B$61=$B$1,IF(Raw!$A$2:$A$61=A3,IF(Raw!$D$2:$D$61>$B$1,Raw!$E$2:$E$61,MAX(IF(Raw!$A$2:$A$61=A3,Raw!$E$2:$E$61))))))

    Or instead of Raw!$D$2:$D$61>$B$1 =($B$1+1)

  11. #11
    Registered User
    Join Date
    12-15-2012
    Location
    Manila
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: How to Get Earliest Time In and Latest Time Out

    tried it and it works well with the log-out....

    will this work as well for the log-ins?

    Thanks!

  12. #12
    Registered User
    Join Date
    08-05-2013
    Location
    USA
    MS-Off Ver
    2010
    Posts
    76

    Re: How to Get Earliest Time In and Latest Time Out

    I'm sorry, I thought the previous formulas worked for the Login and you only needed a revision to the Logout.

    =MIN(IF(Raw!$B$2:$B$61=$B$1,IF(Raw!$A$2:$A$61=A3,Raw!$C$2:$C$61)))

    Does this work?

  13. #13
    Registered User
    Join Date
    12-15-2012
    Location
    Manila
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: How to Get Earliest Time In and Latest Time Out

    Hi!

    First, my warmest thanks for your help!

    The formula works as it was designed... but I am still having problems to those having multiple log-ins and log-outs and their shift passes midnight.

    Like in one case an agent's shift is from 8PM to 5AM. He logs-in on Tuesday at 8PM but accidentally logged-out at 1AM which is a Wedenesday and logs back in immediately. He finishes his shift and logs-out at 5AM. The formula will report that for the Tuesday shift he logs in at 8PM which is correct but will report his logout at 1AM instead of 5AM. The next coloumn for Wednesday will report his log-in as 1AM which is not correct.

    I tried to do any revision possible but to no avail

    Again my biggest thanks!

  14. #14
    Registered User
    Join Date
    08-05-2013
    Location
    USA
    MS-Off Ver
    2010
    Posts
    76

    Re: How to Get Earliest Time In and Latest Time Out

    Bump...
    Sorry, I've been trying to think of a way to do this with a formula. My idea is to check to see if the last log in/log out time was within a certain variance (like 5 minutes) and if so, to disregard that entry, but I've been unable to wrap my head around it.

+ 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. Earliest and Latest Time where cell contains certain text
    By asalbus in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-23-2013, 01:45 AM
  2. Finding earliest date/time
    By Atlanticja in forum Excel General
    Replies: 3
    Last Post: 10-10-2012, 06:37 AM
  3. [SOLVED] Calculate time durations for a roster, and indentify the earliest and latest times
    By bajdr47 in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 08-25-2012, 04:47 PM
  4. Excel 2007 counting of earliest and latest time
    By pvesterberg in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-21-2010, 03:21 AM
  5. Finding the earliest time
    By snmp in forum Excel General
    Replies: 4
    Last Post: 05-29-2009, 07:26 PM

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