+ Reply to Thread
Results 1 to 8 of 8

Lookup closest login/logout time from multiple instances with criteria

  1. 10-16-2017, 08:38 PM #1
    sushant.pan
    sushant.pan is offline
    Registered User
    Join Date
    10-07-2017
    Location
    india
    MS-Off Ver
    2013
    Posts
    3

    Lookup closest login/logout time from multiple instances with criteria

    Hi Folks,

    I think this question has been posted a few times in various versions/variations but was not able to find a formula that suits my needs.

    I am trying to make a login/logout report.Raw data contains multiple instances that is completely random. I want to lookup those time stamps which are less than the reference scheduled time for a person.

    Example: A person's scheduled login time is 5:00 AM and in raw data we get three instances say.(4:58 AM/ 5:01 AM/ 5:15 AM).
    So, in here the ideal time stamp that needs to be looked up would be 4:58 which is less than the scheduled sample.xlsx login time.

    s, basically first we have to lookup for a value which is less than 5 am , i tried some index match formula which can pick closest time but that was not yielding the right value as the closes would be in this case is 5:01 am..


    I hope i am able to explain what i need here, if there is any doubt feel free to ask.

    I have attached a sample file with raw data and report.

    Thanks in advance.
    Reply With QuoteRegister To Reply
  2. 10-16-2017, 08:43 PM #2
    leelnich
    leelnich is offline
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Lookup closest login/logout time from multiple instances with criteria

    OOPs, I just saw your file. Thanks...
    Last edited by leelnich; 10-16-2017 at 08:47 PM.
    Clicking the Add Reputation star below helpful posts is a great way to show your appreciation.
    Please mark your threads as SOLVED upon conclusion (Thread Tools above Post # 1). - Lee
    Reply With QuoteRegister To Reply
  3. 10-17-2017, 01:28 AM #3
    leelnich
    leelnich is offline
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Lookup closest login/logout time from multiple instances with criteria

    Please see Post #4 for a better solution. - Lee
    This solution employs the IFERROR, MOD, and AGGREGATE functions (sub-types SMALL and LARGE). Early Logins/Late Logouts are checked first. If not found, late Logins/early Logouts are checked. Time limits may need some work, right now they're bounded by the date.

    Named ranges: Agent_Name =RAW!$A$2:$A$20000
    Logins =INDEX(RAW!$B$2:$B$20000+RAW!$D$2:$D$20000,)
    Logouts =INDEX(RAW!$C$2:$C$20000+RAW!$E$2:$E$20000,).

    For logins, in G2 and copy down: 
    Please Login or Register  to view this content.
    For logouts, in H2 and copy down: 
    Please Login or Register  to view this content.
    Last edited by leelnich; 10-17-2017 at 04:09 AM.
    Reply With QuoteRegister To Reply
  4. 10-17-2017, 04:25 AM #4
    leelnich
    leelnich is offline
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Lookup closest login/logout time from multiple instances with criteria

    Updated 18 Oct 2017 @ 1:53 AM...

    I found a better way to to keep logins and logouts synchronized and limit the results to plausible candidates. Please see attachment for further details.

    Named ranges: Agent_Name =RAW!$A$2:$A$20000
    Logins =INDEX(RAW!$B$2:$B$20000+RAW!$D$2:$D$20000,)
    Logouts =INDEX(RAW!$C$2:$C$20000+RAW!$E$2:$E$20000,).

    Logins - Paste this ARRAY FORMULA* in cell G2 and copy down: 
    Please Login or Register  to view this content.
    Logouts- another ARRAY FORMULA*, in H2 and copy down: 
    Please Login or Register  to view this content.
    *Always press CTRL+SHIFT+ENTER to confirm entry after pasting or editing an ARRAY FORMULA in the Formula Bar.
    When copying the formula to other cells, make sure the copied cell is not part of the Paste Area.
    Attached Files Attached Files
    Last edited by leelnich; 10-18-2017 at 02:02 AM.
    Reply With QuoteRegister To Reply
  5. 10-18-2017, 10:02 AM #5
    sushant.pan
    sushant.pan is offline
    Registered User
    Join Date
    10-07-2017
    Location
    india
    MS-Off Ver
    2013
    Posts
    3

    Re: Lookup closest login/logout time from multiple instances with criteria

    Hi Lee,

    First of all, I thank you so much for taking your time for figuring this issue out.

    Both the formula works fine for login , but seems like an issue with logout time.

    I tried to run the report with fresh set of raw data , and it is picking undesired value for login time.

    My apologies, i forgot to mention one scenario in the logout part. in case if any employee stretches his shift for 1 or 2 hour it is still picking the closest
    value in reference to logout time.
    So i still need your help with this.

    i am attaching the file with fresh data .

    Thanks in advanceClosest Login LLNv3.xlsx
    Reply With QuoteRegister To Reply
  6. 10-19-2017, 05:36 PM #6
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,532

    Re: Lookup closest login/logout time from multiple instances with criteria

    Attached is a copy of the file attached to post #5 with the data on the 'Raw' sheet put in table form.
    The array entered formula* for logins is:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    The array entered formula* for logouts is:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    *See the note in post #4
    Note that this is only an alternative method to leelnich's solution, not presented as being better.
    Note that the highlighted values in H16:H18 are the same as before with an explanation given. If the explanation isn't satisfactory please help us to understand how the values in F16:F18 are derived.
    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 With QuoteRegister To Reply
  7. 10-21-2017, 06:32 PM #7
    sushant.pan
    sushant.pan is offline
    Registered User
    Join Date
    10-07-2017
    Location
    india
    MS-Off Ver
    2013
    Posts
    3

    Re: Lookup closest login/logout time from multiple instances with criteria

    Hi JeteMc,
    formula is working perfectly fine for logins, but its not working for Logout as ii need the last logout time instance. Current formula picksup the logout time corresponding to login, any help would be appreciated.
    Reply With QuoteRegister To Reply
  8. 10-22-2017, 09:11 PM #8
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,532

    Re: Lookup closest login/logout time from multiple instances with criteria

    If I understand the statement '...i need the last logout time instance. Current formula picksup the logout time corresponding to login...' it would seem that you don't care when the person logged in. If that is the case then modify the array entered formula* in H2 so that it reads:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    *Remember to activate the formula by simultaneously pressing the Ctrl, Shift and Enter keys before copying the formula down.
    Let us know if you have any questions.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Lookup closest time value based on criteria
    By ryanpog1 in forum Excel General
    Replies: 16
    Last Post: 07-10-2019, 09:15 PM
  2. Finding earliest login and latest logout time for a date
    By amohamedfarook in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-28-2016, 03:24 AM
  3. [SOLVED] login & logout attendance sheet for multiple users
    By MdYas in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 10-01-2015, 07:32 AM
  4. Login/logout from multiple places
    By gregersdk in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-23-2015, 04:27 AM
  5. Login/Logout Report Summary with Late Login/Early Logout Indicator
    By kelwin2504 in forum Excel General
    Replies: 0
    Last Post: 02-17-2014, 07:44 AM
  6. log in time out for employee login logout system
    By codillajocyl in forum Excel General
    Replies: 0
    Last Post: 09-11-2013, 10:53 PM
  7. login and logout time , with total login time ,break> 1 hours
    By shrinivasmj in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-12-2013, 05:37 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1