How Do I Search & Test Date Against Values on the Same Line of a Different Sheet?

Question

I'm trying to create an attendance tracker. It takes player checkins and compares them to event locations to see if they match.

I have a list of player checkins that include time, date and location. And a list of Events, with start time, date and location.

I want to be able to compare the 2 data sets and search to find out if the player checked in during any of the events. (Event checkin time is +/- 1 hour)

I was thinking the way to do it would be to use the players checking date to search for a matching event date, find the first match, then compare the locations, see if they match, then see if the time is within range. The issue is that I'm not sure how to guarantee the searches all take place across the same "Event" as opposed to pulling and matching dates and times from different events

I started with checking to see if the times were within range, but I couldn't figure out how to match the Date and times.

Hopefully, that makes sense. Hopefully you guys can help me understand the logic of how to go about doing it.

Here's the link to the google file t play with..

https://docs.google.com/spreadsheets/d/1OZSDju3hRyGyRfFhHJT2PLQ3DBvcfOAT1ZvNxB-J0DQ/edit?usp=sharing

Answer 1

You can try SUMPRODUCT function:

=SUMPRODUCT((B3='Virtual Host Course Upload'!$E$2:$E$5)*(C3='Virtual Host Course Upload'!$D$2:$D$5)*(D3>= 'Virtual Host Course Upload'!$C$2:$C$5-1/24)*(D3<= 'Virtual Host Course Upload'!$C$2:$C$5+1/24))>0