Forum Discussion
IF Function help!
Trying to find a function that will work for the following: If the hour of the day is from 8am to 5pm, then false If the hour of the day is 5pm to 8am, then true. My data is formatted in hours wi...
I believe the clearest approach is to use a couple of spare cells to hold the beginning and end of the working day and name them 'start' and 'end'. The values they actually hold are 1/3 and 35/48 respectively but the significance of such numbers is not that obvious. If you are interested in a single time or access times one by one using a relative reference then you could use the formula
= MEDIAN( Time, Start, End )<>Time
If, on the other hand you want to process an array of times, one could use
= NOT( (timeArray>=Start) * (timeArray<=End) )
or even
= (timeArray<Start) + (timeArray>End)
Of course, you could always replace the Names by hard-wired values and direct cell references if you think that improves things.