Today() and IF Function

[Dave H]

Hello,
I'm trying to set up a formula to return cell C2 if A2 is x and B2 is within
180 days of todays date. Ex:
A B C
1 9/1/08 45784

This is what I've tried:

=IF(A2=x & IF(B2-Today()<=180),C2,"")

Any hints would be appreciated.

Dave

 

[Teethless mama]

=IF(AND(A2="x",B2-TODAY()<=180),C2,"")

 

[Dave H]

It accepts the formula without error but comes up blank in all cases.
This is a cut and paste example:

AY AZ BA
1 9/1/2008 4000
1 11/15/2008 4005
2 11/1/2008 4010

=IF(AND(AY522="1",AZ522-TODAY()<=180),BA522,"")
Is date formatting an issue?

Thanks

 

[David Biddulph]

I assume that your AY column doesn't contain the text string "1", so that's
why your answer is always blank.
If you want to test for the number 1, don't put quotes mark around it.
The formula given was when you said you were looking for x. x is a text
value so needs quote marks around it in the formula. 1 might be either text
or a number, but by default it would be a number, and thus doesn't want
quote marks.

 

[Dave H]

Bingo, that was it. Many Thanks, I appreciate the help

 

[Dave H]

One follow on question. If I want to add one more logic test to the argument
can I nest it in the original formula. So here I want to not return the cell
BA522 if AZ522 falls between the dates of 4/17/09 to 5/13/09. What I tried
was;

IF(AND(AY522=1,AZ522-TODAY()<=364,(AZ522,<>,DATE(2009,4,17:2009,5,13)),BA522,"")

 

[David Biddulph]

1 Perhaps you intended AZ522,<>,DATE... to be written as AZ522<>DATE...
2 DATE doesn't take a syntax like DATE(2009,4,17:2009,5,13)) It takes 3
arguments, and not a range.

Try
=IF(AND(AY522=1,AZ522-TODAY()<=364,OR(AZ522<DATE(2009,4,17),AZ522>DATE(2009,5,13))),BA522,"")

As a helpful hint, when you try an illiegal syntax in a function, when you
try to enter the formula Excel will position the cursor at the first point
where the syntax is illegal (so at the position of the first of your
spurious commas in point 1 above). You can use this to help you to find
what you've done wrong.

 

[Dave H]

Super, again thank you. Good input about the syntax and I did see where it
was hanging up but alas I was too clueless to fix it.