Time comparison formula
 

=if(6:00am<6:00am,true,false)=false And
=if(6:00am11:00am,true,false)=false. Why Do I Get This Result When I
Perform The Following Calculation
=if(6:00am<6:00am11:00am,true,false)=true. Can Someone Tell Me What I
Am Doing Incorrectly.


--
montagu
------------------------------------------------------------------------
montagu's Profile: http://www.excelforum.com/member.php...o&userid=15227
View this thread: http://www.excelforum.com/showthread...hreadid=399029

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:00am11: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.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"montagu" wrote in message
...

=if(6:00am<6:00am,true,false)=false And
=if(6:00am11:00am,true,false)=false. Why Do I Get This Result When I
Perform The Following Calculation
=if(6:00am<6:00am11:00am,true,false)=true. Can Someone Tell Me What I
Am Doing Incorrectly.


--
montagu
------------------------------------------------------------------------
montagu's Profile: http://www.excelforum.com/member.php...o&userid=15227
View this thread: http://www.excelforum.com/showthread...hreadid=399029

You did nothing wrong...

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


Hence, your formula returns TRUE. I am not sure why, but Excel treats
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:00am11:00am),true,false) returns FALSE

HTH


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=399029

David McRitchie Wrote:
something like the following is invalid in any language
6:00am<6:00am11:00am

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,C1B1),true,false) returns FALSE

Bruce


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=399029