I often have a need to compare a date with two other dates and see
whether dateA is between dateB and dateC. I have tried to use the "IF"
function but it doesn't seem to take arguments as complicated as "IF
((A=B) and (A<=C))" which is basically what I am asking. I've been
having to use two different T/F columns and just take the ones that
are TRUE in both, but that is very cumbersome. Is there a more
sophisticated way to ask if A is between B and C?

Thanks...

On Fri, 27 Mar 2009 13:28:15 -0700 (PDT), wrote:

I often have a need to compare a date with two other dates and see
whether dateA is between dateB and dateC. I have tried to use the "IF"
function but it doesn't seem to take arguments as complicated as "IF
((A=B) and (A<=C))" which is basically what I am asking. I've been
having to use two different T/F columns and just take the ones that
are TRUE in both, but that is very cumbersome. Is there a more
sophisticated way to ask if A is between B and C?

Thanks...

Try this formula:

=IF (AND(A1=B1,A1<=C1),"between","not between")

Hope this helps / Lars-Åke

Another one:

=A1=MEDIAN(A1:C1)

--
Biff
Microsoft Excel MVP


wrote in message
...
I often have a need to compare a date with two other dates and see
whether dateA is between dateB and dateC. I have tried to use the "IF"
function but it doesn't seem to take arguments as complicated as "IF
((A=B) and (A<=C))" which is basically what I am asking. I've been
having to use two different T/F columns and just take the ones that
are TRUE in both, but that is very cumbersome. Is there a more
sophisticated way to ask if A is between B and C?

Thanks...

Thanks to both of you! I knew there was something I was missing!

On Mar 27, 5:20 pm, "T. Valko" wrote:
Another one:

=A1=MEDIAN(A1:C1)

Actually I don't think this would work, because I am not always
looking for the MEDIAN of the two dates (plus your formula as written
is self-referential). Lars-Åke's suggestion is exactly what I needed,
but thanks anyway!

Kent

Hello Kent,

Biff's MEDIAN solution works.

Another approach:
=(A1=B1)*(A1<=C1)

Regards,
Bernd

=IF (AND(A1=B1,A1<=C1),"between","not between")
=A1=MEDIAN(A1:C1)

If the date/number in C1 is always greater than or equal to the date/number
in B1 those formulas are checking for the *exact same thing*. The only
difference is the MEDIAN formula returns either TRUE or FALSE

Try it like this and you'll get identical results from both formulas.

=IF(A1=MEDIAN(A1:C1),"between","not between")
=IF(AND(A1=B1,A1<=C1),"between","not between")



--
Biff
Microsoft Excel MVP


wrote in message
...
On Mar 27, 5:20 pm, "T. Valko" wrote:
Another one:

=A1=MEDIAN(A1:C1)

Actually I don't think this would work, because I am not always
looking for the MEDIAN of the two dates (plus your formula as written
is self-referential). Lars-Åke's suggestion is exactly what I needed,
but thanks anyway!

Kent