Time comparison formula

M

montagu

=if(6:00am<6:00am,true,false)=false An
=if(6:00am>11:00am,true,false)=false. Why Do I Get This Result When
Perform The Following Calculatio
=if(6:00am<6:00am>11:00am,true,false)=true. Can Someone Tell Me What
Am Doing Incorrectly
 
D

David McRitchie

Hi montagu,

The syntax for both IF and AND differs between
Worksheet Functions and VBA.

something like the following is invalid in any language, I've used
6:00am<6:00am>11:00am,
also you asked for your value to be less than 6AM and greater than 11AM
in your formula. I expect you mean to fall between those two values.

syntax:
=IF(AND(condition1,condition2), true, false)

possible solution to your question
=IF(AND(A1>=TIME(6,0,0),A1<TIME(11,0,0)), "within range", "out of range")

suggest you look in HELP for more information on both
IF Worksheet Function
AND Worksheet Function
now that you should be able to read it better.
 
S

swatsp0p

You did nothing wrong...

If you follow the progression of evaluating this formula, you will fin
that the first step is to eval 6<6 (time is irrelavent at this point
which returns TRUE. Then evaluate TRUE>11 and the result is TRUE. Fo
arguments sake, change this to 7<6=FALSE. FALSE>11 also returns TRUE.


Hence, your formula returns TRUE. I am not sure why, but Excel treat
both TRUE and FALSE as > any number (even a negative).


You may want to break up your comparisons, as such:

=IF(AND(6:00am<6:00am,6:00am>11:00am),true,false) returns FALSE

HT
 
S

swatsp0p

David said:
something like the following is invalid in any language
6:00am<6:00am>11:00am
Click to expand...

David, I assumed the OP was actually using cell references or the like
in their formula, not the actual listed values, and my reply was
written in the same format as the OP's.

My assumption was, e.g.:

A1=6:00AM
B1=11:00AM
C1=6:00AM

=IF(AND(C1<A1,C1>B1),true,false) returns FALSE

Bruce
 
Top