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!
Bookmarks