+ Reply to Thread
Results 1 to 17 of 17

Lookup closest time value based on criteria

  1. #1
    Registered User
    Join Date
    05-13-2010
    Location
    Manila, Philippines
    MS-Off Ver
    Excel 2010
    Posts
    14

    Lookup closest time value based on criteria

    Hi,

    I would like to ask if anyone can help me come up with a formulat to use for this, any help would be appreciated so much.

    I want to be able to find the closest value based on a predefined criteria. Specifically, this involves employee logins and logouts. The logins are based on a "raw" file and the shift schedules are predefined. The problem is there are sometimes multiple logins/logouts when the employee experiences problems with the systems (the logins are based on a software installed on the employee's computer).

    I found that VLOOKUP cannot always work in this case since it always returns the first matching lookup value, which is not always the correct login or logout time.

    One challenge is that some employees work on the night shift which means that their shift crosses-over to the next day; and if they re-login when the day crosses-over, it would seem that they are logging in for the next day's shift.

    I think the best solution is to have a formula that will look up the closest time from the specifed "shift time" each day.

    I'm not sure if i'm this makes sense to anyone else so I'm attaching a sample file for reference.

    Thanks in advance!
    Attached Files Attached Files
    Last edited by ryanpog1; 10-11-2010 at 01:35 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Lookup time value based on criteria

    I think the Vlookup works for column D as it picks up the first entry for the day's shift.

    for column E you can use:

    =INDEX(RAW!$A$2:$D$17,MATCH(2,INDEX(1/(RAW!$A$2:$A$17=$B$2&TEXT($B5,"m/d/yy")),0)),4)

    copied down to pick up last entry for the day.

    On your comment for the 14th.. on that day's entry in the Raw data, you have midnight.. which is 12:00 next day ..why do you need it to be same day's date?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    05-13-2010
    Location
    Manila, Philippines
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Lookup time value based on criteria

    Hi NBVC,

    Thanks a lot for your help!


    Quote Originally Posted by NBVC View Post
    I think the Vlookup works for column D as it picks up the first entry for the day's shift.

    for column E you can use:

    =INDEX(RAW!$A$2:$D$17,MATCH(2,INDEX(1/(RAW!$A$2:$A$17=$B$2&TEXT($B5,"m/d/yy")),0)),4)

    copied down to pick up last entry for the day.

    On your comment for the 14th.. on that day's entry in the Raw data, you have midnight.. which is 12:00 next day ..why do you need it to be same day's date?
    This formula would work most of the time for the "logout" time since it gets the last "logout" time. Unfortunately because of the way the raw data is structured, there will be times when the last logout time will not be recognized.

    The raw data stores both the "login" and "logout" time in one row. And the way the logout time is being searched is through the date on the login time. In the case of Sept 20, the employee for some reason has to re-login at 3:05 and logout at 7:00am. The raw data would then contain the sept 21 on the 3:05am re-login which would make the 7:00am logout time not the last logout for the Sept 20 shift.

    This is the same scenario for the 14th wherein the logout time should have been 7am but is seen as the logout for the next day, 15th.

    This is why I believe the best solution is to define the shift schedule and find the closest login and logout time.

    Example:

    Shift Start - Sept 4 11:30pm

    possible correct login time in RAW:

    Sept 4 8:00am
    Sept 5 12:00am

    Based on this, the correct login time should be Sept 5 12:00am (The employee was late by 30 mins). This is because this is the closest time to the defined shift of 11:30pm eventhough it is already on the next day; just 30 mins difference from the defined shift schedule compared to the one on the same date but is more than 15 hours difference.


    Hope this still makes sense and Thanks again for all your help!
    Last edited by ryanpog1; 09-27-2010 at 09:06 AM.

  4. #4
    Registered User
    Join Date
    05-13-2010
    Location
    Manila, Philippines
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Lookup time value based on criteria

    double post... sorry
    Last edited by ryanpog1; 09-27-2010 at 09:07 AM.

  5. #5
    Registered User
    Join Date
    05-13-2010
    Location
    Manila, Philippines
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Lookup time value based on criteria

    Hi All,

    Sorry if I seem too persistent, but I need to get this right for a project on a deadline. I would appreciate any suggestions.


    Thanks!

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Lookup time value based on criteria

    Your request is a bit confusing.

    Perhaps you can load up another example with new samples and show the desired results with explanations.

  7. #7
    Registered User
    Join Date
    05-13-2010
    Location
    Manila, Philippines
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Lookup time value based on criteria

    Quote Originally Posted by NBVC View Post
    Your request is a bit confusing.

    Perhaps you can load up another example with new samples and show the desired results with explanations.
    My apologies if I'm not able to explain it a bit clearer. I guess to make it simple, what I would want is to get a formula that would choose the closest time from a "Raw" data and the criteria would be a predefined one.

    Ex:

    Column A: (This would be the predefined shift schedule)

    A1: 9/14 11:00 PM
    A2: 9/15 11:00 PM

    Column B: (This is where the result of the formula would show)


    RAW data:

    Column A: (This would contain all "login entries"


    9/14 1:00 AM
    9/14 10:00 AM
    9/14 10:30 PM
    9/15 3:00 AM
    9/15 7:00 AM
    9/16 12:15 AM


    Based on the details above the results of the formula should be:

    9/14 - the login would return as '9/14 10:30 PM' since this is the closest time form the predefined shift schedule on A1

    9/15 - the login would return as '9/16 12:15 AM' since this is the closest time from the predefined shift schedule on A2. Note that this already goes over to the next day but since this is the closest time from A2, this is logically the login time of the employee.


    Using vlookup on both cases would return the first 9/14 and 9/15 dates respectively since it would follow the 'first in' rule; which is not the correct login time for the employee.

    Hope this makes better sense.

    I appreciate everything, thanks!

  8. #8
    Registered User
    Join Date
    05-13-2010
    Location
    Manila, Philippines
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Lookup time value based on criteria

    Hi All,

    In case anyone would be interested, I was able to come up with the formula that works for what I'm doing.

    Basically, I started with this:

    {=INDEX(ARRAY,MATCH(MIN(ABS(ARRAY-A2)),ABS(ARRAY-A2),0))}
    (where A2 is the lookup value)

    This returns the closest value (date and time) from the 'RAW' sheet based on the lookup value. The only challenge is to filter the "ARRAY" to only look for the entries for a specific person. The solution I see fit is to make the "ARRAY" dynamic by replacing all the "ARRAY" on the formula above with this:

    (INDIRECT("RAW!"&ADDRESS(MATCH(A$1,LoginName,0),2)&":"&ADDRESS(MATCH(A$1,LoginName,0)+COUNTIF(LoginName,F$1)-1,2))
    (where LoginName is Named Range of all the logins on the RAW sheet)

    This narrows down the "ARRAY" to only the rows that contain the name of the person (A1).

    This may look a bit messy but it got the job done for me. Hope someone else may have a good use for this.

    Thanks!

  9. #9
    Registered User
    Join Date
    05-13-2010
    Location
    Manila, Philippines
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Lookup time value based on criteria

    Just to add...

    I found the INDIRECT approach to be too brute so I searched for cleaner way to come up with a dynamic array and found one using the OFFSET function:

    OFFSET(LoginStart,MATCH(E$7,LoginName,0)-1,1,COUNTIF(LoginName,E$7),1)
    (where LoginName is Named Range of all the logins on the RAW sheet and LoginStart is the first cell on this column)


    got to love the internet!

  10. #10
    Registered User
    Join Date
    05-13-2010
    Location
    Manila, Philippines
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Lookup time value based on criteria

    Just when I thougth everything is all good an dandy, it turns out that using array formulas would be an issue when sharing a workbook. The issue is the cell cannot be edited at all if it contains an array formula if the workbook is shared. The thing is I want to use this formula to automate the task of selecting logins and logouts of the employees, but I still want manual adjustments to be possible.

    So I'm calling out those who have the knowledge (and the time) to provide some help in coming up with a non-array formula that would come up with the same result as the array formula below.

    {=INDEX(ARRAY,MATCH(MIN(ABS(ARRAY-A2)),ABS(ARRAY-A2),0))}
    (where A2 is the criteria/lookup value)


    Any help would be much appreciated.

    Thanks!

  11. #11
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Lookup closest time value based on criteria

    Try:

    =INDEX(array,MATCH(TRUE,INDEX(MIN(INDEX(ABS(array-A2),0))=INDEX(ABS(array-A2),0),0),0))

  12. #12
    Registered User
    Join Date
    05-13-2010
    Location
    Manila, Philippines
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Lookup closest time value based on criteria

    Quote Originally Posted by NBVC View Post
    Try:

    =INDEX(array,MATCH(TRUE,INDEX(MIN(INDEX(ABS(array-A2),0))=INDEX(ABS(array-A2),0),0),0))
    Great! Where do you guys come up with this stuff?!?! This solves everything for me. Thanks a million!

  13. #13
    Registered User
    Join Date
    06-29-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Lookup closest time value based on criteria

    Hi,

    I have the same project as yours.

    Would you be kind enough to share with me how you solved this?

    Thanks!

  14. #14
    Registered User
    Join Date
    07-23-2017
    Location
    Philippines
    MS-Off Ver
    2013
    Posts
    4

    Re: Lookup closest time value based on criteria

    Hi All,

    I just badly need this formula but I can't transfer it to my Excel, Can you please give me a sample template.
    Same Scenario

    I just need to find the nearest actual arrival (TIME) ,based on the criteria in column A (Shop Name), column B (Approved Time), & Column C (Actual Arrival).

    I really appreciate your feedback.

    thanks a lot.

  15. #15
    Registered User
    Join Date
    07-23-2017
    Location
    Philippines
    MS-Off Ver
    2013
    Posts
    4

    Re: Lookup closest time value based on criteria

    Hi,

    Do you a solved sample template for this, please.
    thank you.

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,474

    Re: Lookup closest time value based on criteria

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  17. #17
    Registered User
    Join Date
    07-10-2019
    Location
    France
    MS-Off Ver
    2010
    Posts
    1

    Re: Lookup closest time value based on criteria

    Good content here

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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