How could I check if a Time value falls between two other time values?
I want to essentially establish if a premises is open or not at
various times of the day.

My time values are input as hh:mm and have no date. The issue I am
having is that opening times can vary, which may include past 12:00am
eg. 7:00am to 2:00am (the following day). So if I check if the
premises is open during the hour 11:00pm I can construct a formula
test if it is >= than Opening time and <= Closing time, but this won't
work past 12:00am (see CF below)

Do I need to combine a dd aspect to the hh:mm values and if so how
could I do that?

Ultimately what I want to do is change the background colour of the
Time value to Blue

A previous post offered the following CF =AND(A8>=Sheet2!F7,A8<=Sheet2!
H7), but that doesn't get me past 12:00am

A8= Time
F7 = Opening Time
H7= Closing Time
I do have dates of the week referenced in C1:C7

=AND(A8+(A8<Sheet2!F7)>=Sheet2!F7,A8+(A8<Sheet2!F7)<=Sheet2!H7+(Sheet2!H7<Sheet2!F7)) 
should be one way.

You might be able to simplify it using the MOD function, but I failed in my 
first attempts to do so, hence I'll leave it as an exercise for the 
interested reader.
--
David Biddulph

Seanie wrote:
> How could I check if a Time value falls between two other time values?
> I want to essentially establish if a premises is open or not at
> various times of the day.
>
> My time values are input as hh:mm and have no date. The issue I am
> having is that opening times can vary, which may include past 12:00am
> eg. 7:00am to 2:00am (the following day). So if I check if the
> premises is open during the hour 11:00pm I can construct a formula
> test if it is >= than Opening time and <= Closing time, but this won't
> work past 12:00am (see CF below)
>
> Do I need to combine a dd aspect to the hh:mm values and if so how
> could I do that?
>
> Ultimately what I want to do is change the background colour of the
> Time value to Blue
>
> A previous post offered the following CF
> =AND(A8>=Sheet2!F7,A8<=Sheet2! H7), but that doesn't get me past
> 12:00am
>
> A8= Time
> F7 = Opening Time
> H7= Closing Time
> I do have dates of the week referenced in C1:C7

David many thanks, it appears to work like a dream. Previously every
time I tried to modify your previous CF I got even more confused

One twist on this which I am trying to cover for closing times - my
Hours to check in A8 are in whole hours i.e. 7:00pm; 8:00pm etc, but
my Closing times can be part of eg. 7:30pm; or 8:00pm or 8:30pm etc.
How could I highlight if the Hour tested is within + 30 mins of
closing

Eg Test for 3:00am, if the closing Time was 2:30am, here I wish to
have the Test time i.e. 3:00am highlighted as it falls within +30mins
of closing

The Opening is fine on 1/2 hours as all I am testin if the Time is >
than Opening