IF Function help!

Copper Contributor

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 with military time only in the field.

Example:  23:06

I want another column with the function to say that 23:06 is true because it's after business hours

3 Replies

@cristacan 

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.

 

@cristacan This formula will show TRUE if the time value is between 8 am and 5 pm and FALSE if it is not. You don't need an IF statement to return TRUE or FALSE.

 

=AND(MOD(A1,1)>=TIME(8,0,0),MOD(A1,1)<=TIME(17,0,0))

 

The format of the cell with the time value is not relevant.

This formula returns TRUE, if the time in A1 is before 8:00 AM or after 5:00 PM; otherwise, it returns FALSE:
=OR(A1<TIME(8,0,0),
A1>TIME(17,0,0))