Finding values within two ranges

[NFT86]

Hello,

I am going to try to explain this the best I can, and it's possible there's no real easy way to do this, so bear with me.


Basically, I have people who wrote down that they attended a location on a certain date at a certain time, and I have their start and end time.

So for example my person data has something like this:


Arrive: 1/1/12 7:00 AM,
Leave: 1/1/12 10:00 AM

Arrive: 1/1/12 3:00 PM
Leave: 1/1/12 4:00 PM

Arrive: 1/4/12 8:00 PM
Leave: 1/4/12 10:15 PM

I also have those fields formatted as text and as decimals so I can work with whatever I need.


The next portion is a list of events that occur at these same locations. Here are some examples.

Event 1

Start: 1/1/12 8:15 AM
End: 1/1/12 12:15 PM


Event 2

Start: 1/4/12 3:00 PM
End 1/4/12 8:30 PM



What I need to find out is if the person was present at the location during any of the events.

A person may write down more than one visit to the location in a day. The events only happen once per day. I'm sure there's a pretty manual way to do this, however the list of people and events spans an entire year and it will be too time consuming to do it that way.


I feel like there is a way to do this and that I'm close, but I can't seem to figure it out.

Any suggestions or help would be much appreciated!


Thank you!!

NFT

[newdoverman]

This works with the limited data supplied. If you add events, those events have to be added to the formula but I think that this should give you an idea of how to solve this problem.

[NFT86]

Thanks for the help! This can work, but because the list of events was so long, I was hoping there might be a quicker way than entering them each into a formula. I'll definitely use this though, so thank you!