If Statement - Date Calculations
 

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$31999,$B$1<"1/1/2002",$A$31999),"Monthly","None")

=IF(AND($A$1"12/31/1999",$A$31999,$B$1"12/31/1999",$A$31999),"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

If Statement - Date Calculations
 

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


"Tony" wrote in message ...
|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$31999,$B$1<"1/1/2002",$A$31999),"Monthly","None")
|
| =IF(AND($A$1"12/31/1999",$A$31999,$B$1"12/31/1999",$A$31999),"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

If Statement - Date Calculations
 

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$112/31/1999
is equal to
$B$10,000193645

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

|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$31999,$B$1<"1/1/2002",$A$31999),"Monthly","None")
|
| =IF(AND($A$1"12/31/1999",$A$31999,$B$1"12/31/1999",$A$31999),"Monthly","None")
|

If Statement - Date Calculations
 

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 A31999 twice in each formula, that doesn't
effect the result it's just not necessary.
--
Thanks,
Shane Devenshire


"Tony" wrote:

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$31999,$B$1<"1/1/2002",$A$31999),"Monthly","None")

=IF(AND($A$1"12/31/1999",$A$31999,$B$1"12/31/1999",$A$31999),"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