Checking a date/number between two others

[kmpintj]

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...

 

[Lars-Åke Aspelin]

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

 

[T. Valko]

Another one:

=A1=MEDIAN(A1:C1)

 

[kmpintj]

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

 

[kmpintj]

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

 

[Bernd P]

Hello Kent,

Biff's MEDIAN solution works.

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

Regards,
Bernd

 

[T. Valko]

=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

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