Checking for time overlaps against given points in the day

Hi all

I have been trying to solve a problem using excel formulas but I think I need some VBA code to make the formula repeat - unfortunatly this is beyond my excel knowledge! I'd really appreciate some help, been stuck on this for a while now.

The problem is as follows:

There are several sensors which can exist in either two states; "Taken" or "Empty". The data I have gives me the Start and End Time of when a sensor is Taken. I'd like to evaluate the Start/End time for each "Taken" period against a specific time of day (a Reference Time). I will then repeat this for different Reference Times throughout the day. I will do this independently for each sensor and then analyse the end result to see how many of the sensors where taken at different points in the day.

I have a formula which returns a response of "Taken" if the Start Time is less than (or equal to) the Referenced Time AND the End Time is greater than (or equal to) the Referenced Time. This is repeated for each Start and End Time against a given Reference Time. Independently for each sensor.

=IF(AND((IF(AND($B$5<=K5,($K$5<=$C$5)),$B$5,(IF(AND($B$6<=$K$5,$K$5<=$C$6),$B$6,IF(AND($B$7<=$K$5,$K$5<=$C$7),$B$7,"end")))))<=$K5,$C$5>=$K5),"Taken", "Empty")

Where K5 is the Reference Time
B5 is the Start Time for the first registered "Taken" session, and C5 is the End Time for the first registered "Taken Session" (Sensor A)
B6/C6... B7/C7 then repeats the process for each subsequent Start/End Time (Sensor A).


The problem is I have up to 30-50 "Taken" session for each sensor each day, and I reach the character limit very quickly if I keep adding nested IF functions in this way.

I need some code (?) to repeat the process of checking to see if the Start/End time overlaps with the Reference Time until all Start/End times have been exhausted.

Thanks!!!