Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check if Time falls between ?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check if Time falls between ?
=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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check if Time falls between ?
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check if Time falls between ?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to check if a date falls within a range(2 cells: Start/End Dat | Excel Worksheet Functions | |||
check whether a date falls in a range | Excel Discussion (Misc queries) | |||
how to check if date falls within range | Excel Worksheet Functions | |||
Can I check if a date falls between a range then sum other # if tr | Excel Worksheet Functions |