Advance lookup, multiple conditons, date and time between

[Komarov]

Hi, I need a help with advanced lookup formula.
What i need to achieve is record IN and OUT time according to swipe times provided by security officers (Im transfering this data into pivot table).
Which looks like that:
pivot.jpg
Data in Pivot table is sorted from newest to oldest. It is not an issue for day shifts.

The problem that im having is Night shifts (marked as "Green" and "Yellow")
Screenshot 2022-05-12 113338.jpg

Formula needs to look for a TIME(from pivot table) where,
Action = "EXT Main Gate In (13) (In)",
IF Shift = "Yellow" or "Green" then TIME BETWEEN 17:00 AND 19:00
And date = date from master sheet.


Really similiar for OUT Time, the only difference is gonna be date = date from master sheet +1 and time between 05:00 and 07:00.

[Bernie Deitrick]

I think you uploaded the wrong workbook.....

[Komarov]

Yes, i did. Sorry!
Updated.

[Bernie Deitrick]

Your sample is not really a sample of your data - show the data table that you have with multiple data points, and the result that you want, with an explanation of why that result is chosen. Why do you choose 17:40 out of all of these values?

Card Name Date Time Action
user1 9/19/2021 1:43 EXT Main Gate In (13) (In)
user1 9/19/2021 4:25 EXT Main Gate In (13) (In)
user1 9/19/2021 17:40 EXT Main Gate In (13) (In)
user1 9/19/2021 20:35 EXT Main Gate In (13) (In)
user1 9/19/2021 20:54 EXT Main Gate In (13) (In)
user1 9/19/2021 22:07 EXT Main Gate In (13) (In)
user1 9/20/2021 1:20 EXT Main Gate In (13) (In)
user1 9/20/2021 4:28 EXT Main Gate In (13) (In)
user1 9/20/2021 5:59 EXT Main Gate In (13) (In)

[Komarov]

This data is from the night shift, they are starting at 18:00 and finishing at 06:00 next day.

user1 9/19/2021 17:40 EXT Main Gate In (13) (In)

This result is choosen, because thats the gate to the parking lot, i need to record time when users gonna arrive on a site. Ideally they need to clock between 17:00 and 18:00, but they might be late to work as well, that's why i want to record first swipe time between 17:00 and 19:00.
Same with the clock out time, which is gonna be first swipe time between 05:00 and 07:00. (Some users will be clocking out couple minutes after 6, and some of them usally around 5:40.).