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.