Is Time Within Range

[rrstudio2]

We are trying to determine how many orders are queued during
particular times and have a spreadsheet that list start and finish
times. I added some other columns that list specific times we are
wanting to know how many orders were queued at that specific time.
So I filled in the values that I would expect in this sample:

| Was in Queue at: |
Start | Finish | 08:00 | 22:15 | 00:00 |
07:50 08:03 Y N N
22:00 22:27 N Y N
23:55 00:15 N N Y

The formula I am currently doing this with is not pretty and I think
there is a simplier way to do this. The last example seems to trick
me up because you can't just check if the start time is less than the
queue time AND if the finish time is greater than the queue time. How
would you write a formula to do this?

Thanks,
Andrew V. Romero

 

[Roger Govier]

Hi

I haven't tested all combinations, but the following seems to return the
correct number of True's and False's
With your table in A1:E4, enter in C2
=AND(MOD($A2+1/24,1)<=C$1+1/24,$B2>C$1)
Copy across and down as required

--
Regards
Roger Govier

We are trying to determine how many orders are queued during
particular times and have a spreadsheet that list start and finish
times. I added some other columns that list specific times we are
wanting to know how many orders were queued at that specific time.
So I filled in the values that I would expect in this sample:

| Was in Queue at: |
Start | Finish | 08:00 | 22:15 | 00:00 |
07:50 08:03 Y N N
22:00 22:27 N Y N
23:55 00:15 N N Y

The formula I am currently doing this with is not pretty and I think
there is a simplier way to do this. The last example seems to trick
me up because you can't just check if the start time is less than the
queue time AND if the finish time is greater than the queue time. How
would you write a formula to do this?

Thanks,
Andrew V. Romero

__________ Information from ESET Smart Security, version of virus
signature database 4811 (20100127) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4811 (20100127) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Fred Smith]

Here's a formula which meets the requirements listed:
=IF(AND(C$1+(C$1<$A$2)>$A2,(C$1+(C$1<$A$2))<($B2+($B2<$A2))),"Y","N")

It assumes that the first start time will always be the earliest time in the
day.

Regards,
Fred

 

[Herbert Seidenberg]

Excel 2007 Table
Count periods active at a certain time of day.
Consolidate several days with one day wrap-around chart.
Gantt type chart with movable ruler.
Not simpler.
http://www.mediafire.com/file/0ttxytqj0wm/01_29_10.xlsx