Finding if a certain time falls between two other times:

[agrandstaff]

I am trying to find a formula that will tell me whether a specific
time falls between two other times, and really, ultimately, to count
up the number of times a certain time falls between other times in a
column: Easier to explain through an example.

A B C
8:30 AM 9:30 AM 9:00 AM
8:45 AM 9:45 AM
11:00 AM 12:00 PM
11:00 AM 12:00 PM
11:15 AM 12:15 PM
11:30 AM 12:30 PM
12:00 PM 1:00 PM

I've been trying to use a mix of COUNTIFs, and COUNT and IF, etc to
say . . .how many times are there were the time in C1 falls between
the start time in column A and the end time in the same row in Column
B. The answer with the example above would be "2" as 9:00 AM falls
between 8:30 AM and 9:30 AM, and 8:45 AM and 9:45 AM. I've tried a
bunch of things that try to see find where column a is GREATER THAN C1
(9:00 AM) and the corresponding time in column b is LESS THAN C1, none
of it's working. Any ideas?

 

[Peo Sjoblom]

Here you are


=SUMPRODUCT(--(A1:A7<=C1),--(B1:B7>=C1))

--


Regards,


Peo Sjoblom