# Lookup closest time value based on criteria

1. ## 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.

2. ## 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?

3. ## Re: Lookup time value based on criteria

Hi NBVC,

Thanks a lot for your help!

Originally Posted by NBVC
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!

4. ## Re: Lookup time value based on criteria

double post... sorry

5. ## 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. ## 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. ## Re: Lookup time value based on criteria

Originally Posted by NBVC
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. ## 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:

(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. ## Re: Lookup time value based on criteria

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:

(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. ## 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. ## 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. ## Re: Lookup closest time value based on criteria

Originally Posted by NBVC
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. ## 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. ## 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).

thanks a lot.

15. ## Re: Lookup closest time value based on criteria

Hi,

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

16. ## 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.

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.

17. ## Re: Lookup closest time value based on criteria

Good content here

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

#### 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