If Statement - Date Calculations

[Tony]

I have an IF statement that uses dates to determine which value is displayed
in a cell. I am confused as to whether or not to use quotes (" ") in the
formula. With the quotes it works and without the quotes it doesn't work.
Also, if I change the dates and they are greater than 1/1/2002, the second
formula doesn't work.

Here is the formula:
=IF(AND($A$1<"1/1/2002",$A$3>1999,$B$1<"1/1/2002",$A$3>1999),"Monthly","None")

=IF(AND($A$1>"12/31/1999",$A$3>1999,$B$1>"12/31/1999",$A$3>1999),"Monthly","None")


Col A
Col B
Row 1 1/1/1993 1/1/1992
Row 2 $153,564.00
$38,595.00
Row 3 $10,000.00 $2,000.00

If I change the dates to greater than 12/31/2001 than the formula doesn't
work

 

[Niek Otten]

1. Use complete dates, like 12/31/1999, not just a year, like 1999.
2. It is easier to put each of the dates to compare with (1/1/2002 etc) as date in a cell and compare to that cell, like
=IF(AND($A$1<C1, etc

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


|I have an IF statement that uses dates to determine which value is displayed
| in a cell. I am confused as to whether or not to use quotes (" ") in the
| formula. With the quotes it works and without the quotes it doesn't work.
| Also, if I change the dates and they are greater than 1/1/2002, the second
| formula doesn't work.
|
| Here is the formula:
| =IF(AND($A$1<"1/1/2002",$A$3>1999,$B$1<"1/1/2002",$A$3>1999),"Monthly","None")
|
| =IF(AND($A$1>"12/31/1999",$A$3>1999,$B$1>"12/31/1999",$A$3>1999),"Monthly","None")
|
|
| Col A
| Col B
| Row 1 1/1/1993 1/1/1992
| Row 2 $153,564.00
| $38,595.00
| Row 3 $10,000.00 $2,000.00
|
| If I change the dates to greater than 12/31/2001 than the formula doesn't
| work

 

[Ollie4]

for your first question
Not using quotes in 12/31/1999 will result in 0,000193645. (12 divided
by 31 divided by 1999) With quotes it is a date

$B$1>12/31/1999
is equal to
$B$1>0,000193645

$B$1>"12/31/1999"
is equal to the date

 

[ShaneDevenshire]

Hi Tony,

First what dates are you changing - the ones in the formulas or the ones in
cells A1:B1?

If you are changing the formula dates - they work fine for me! If the date
are not greater than 12/31/1999 then they are not greater than 12/31/2001.

Next point - you are testing A3>1999 twice in each formula, that doesn't
effect the result it's just not necessary.