IF statements with <= dates?

[Jason O]

Hi,

I am trying to incorporate <= and >= into an IF statement but using dates
and not numerical values (although I am aware Excel in effect turns dates
into numbers). For example, if cell A1 has a date between 01/03/04 and
31/08/04 I want to return one value and if it has a date between 01/09/04
and 28/02/05 then return another. I have tried the following:

=IF(A1>=01/03/04 &A1<=31/08/04,"north","south")

However this does not work and I have checked that A1 is formatted as
'date'.If cell A1 contains a date within the "north" paramaters "south" is
returned? I know I must be doing something blindingly obvious and/or stupid
so any assistance is greatly appreciated. And how would the following
criteria be included into the same IF statement:

=01/09/04 and <=28/02/05 then return "south"
=01/03/05 and <=31/08/05 then return "east"
-01/09/05 and <=28/02/06 then return "west"

MANY TIA,

Jason
____

I know a VLOOKUP (true) may be a better way of doing it but knowing how to
do it this way will help my general understanding. Cheers!

 

[JE McGimpsey]

Hi,

I am trying to incorporate <= and >= into an IF statement but using dates
and not numerical values (although I am aware Excel in effect turns dates
into numbers). For example, if cell A1 has a date between 01/03/04 and
31/08/04 I want to return one value and if it has a date between 01/09/04
and 28/02/05 then return another. I have tried the following:

=IF(A1>=01/03/04 &A1<=31/08/04,"north","south")

However this does not work and I have checked that A1 is formatted as
'date'.If cell A1 contains a date within the "north" paramaters "south" is
returned? I know I must be doing something blindingly obvious and/or stupid
so any assistance is greatly appreciated. And how would the following
criteria be included into the same IF statement:

First, "&" is the concatenation operator, which produces a string.
You're looking for the AND() function.

XL interprets

01/03/04

as 1 divided by 3 divided by 4, or 1/12. So, based on operator
precedence (division higher than concatenation higher than comparision),

A1>=01/03/04 & A1<=31/08/04

will calculate 1/3/4 (1/12, or 0.0833333333333333) and 31/8/4 (31/32, or
0.96875). It will then concatenate 1/12 and A1, so if A1 = 37357 (i.e,.
today), the partial evaluation will be

A1>=0.083333333333333337357<=0.96875

The comparisons are then done left to right. Since the middle argument
is a string, the comparisons will always return FALSE.

You can force XL's implicit coercion of text strings to numeric values
to coerce a text string to a date:

=IF(AND(A1>="01/03/04", A1<="31/08/04"), "north","south")

Or, you can use the DATE() function to return a date:

=IF(AND(A1>=DATE(2004,3,1), A1<=DATE(2004,8,31)),"north","south")

 

[Jason O]

First, "&" is the concatenation operator, which produces a string.
You're looking for the AND() function.

XL interprets

01/03/04

as 1 divided by 3 divided by 4, or 1/12. So, based on operator
precedence (division higher than concatenation higher than comparision),

A1>=01/03/04 & A1<=31/08/04

will calculate 1/3/4 (1/12, or 0.0833333333333333) and 31/8/4 (31/32, or
0.96875). It will then concatenate 1/12 and A1, so if A1 = 37357 (i.e,.
today), the partial evaluation will be

A1>=0.083333333333333337357<=0.96875

The comparisons are then done left to right. Since the middle argument
is a string, the comparisons will always return FALSE.

You can force XL's implicit coercion of text strings to numeric values
to coerce a text string to a date:

=IF(AND(A1>="01/03/04", A1<="31/08/04"), "north","south")

Or, you can use the DATE() function to return a date:

=IF(AND(A1>=DATE(2004,3,1), A1<=DATE(2004,8,31)),"north","south")

Wow, I always learn so much from your replies - many, many thanks.

Can more then 2 conditions be included in the one AND( )? Or would you need
to continue the staement with ELSE and another IF AND ?

Cheers,

Jason
____

 

[Edwin Tam]

For the AND function, you can include several tests. For example,

=AND(A1>2, B1>3, C1>4)

In the above example, all three conditions will need to be satisfied in
order to return TRUE.

Regards,
Edwin Tam
(e-mail address removed)
http://www.vonixx.com

 

[JE McGimpsey]

Can more then 2 conditions be included in the one AND( )? Or would you need
to continue the staement with ELSE and another IF AND ?

From XL Help ("AND"):

AND
Returns TRUE if all its arguments are TRUE; returns FALSE if one or more
arguments are FALSE.

Syntax
AND(logical1,logical2, ...)

Logical1, logical2, ... are 1 to 30 conditions you want to test that
can be either TRUE or FALSE.