IF, AND, OR?

[spendloa]

Good morning all...

Column C always holds a date and column D may (or may not) hold a date. Column E shows a number of days. What I would like to do is something like this:

Return a "1" -- if there is a date in culumn D (this should be checked first) AND the month in D = 1 AND E>8 OR if there is no date in culumn D AND the month in C = 1 AND E>8 -- otherwise return a "0"

I have this and can't work out what is wrong with it:

=IF(AND(MONTH(D5)=1,E5>8),1,IF(MONTH(D5)="",(IF(AND(MONTH(C5)=1,E5>8),1,0)),0))

It keeps returning 1's where they shouldn't be.

As usual, your assitance very much apprecated.
Happy new year!
Al

[WillR]

Try this

=IF(OR(AND(MONTH(D5)=1,E5>8),(AND(MONTH(D5)="",MONTH(C5)=1,E5>8))),1,0)

Hope it helps

[Aladin Akyurek]

=IF(AND(D5<>"",MONTH(D5)=1,E5>8),1,0)

or better:

=(D5<>"")*(MONTH(D5)=1)*(E5>8)

WillR's will be troubled when D5 is empty. However, the foregoing is not trouble-free. A 1 in D5 will pass the MONTH test.

[spendloa]

Cheers, we have sorted it now with the following:

=IF(AND(MONTH($D9)=1,AD9>8,D9>0),"1",IF(AND($D9=0,MONTH($C9)=1,AD9>8),"1","0"))


The problems occured because excel returns a 1 from the "month" function even when there is no date present.

Thanks again

[Aladin Akyurek]

Originally Posted by spendloa

Cheers, we have sorted it now with the following:

=IF(AND(MONTH($D9)=1,AD9>8,D9>0),"1",IF(AND($D9=0,MONTH($C9)=1,AD9>8),"1","0"))


The problems occured because excel returns a 1 from the "month" function even when there is no date present.

Thanks again

You don't need to be that long. See my post. BTW, it's not good idea putting 1 and 0 in between double quotes which transform into non-numbers.

[WillR]

Originally Posted by Aladin Akyurek

=IF(AND(D5<>"",MONTH(D5)=1,E5>8),1,0)

or better:

=(D5<>"")*(MONTH(D5)=1)*(E5>8)

WillR's will be troubled when D5 is empty. However, the foregoing is not trouble-free. A 1 in D5 will pass the MONTH test.

Aladin,

Can I ask how your formulas deal with the fact that if D5 has no data, then a 1 should still be returned by the formula if C5's month is = 1 ? I admit your formulas are much cleaner than my effor, however I can't see the C5 evaluation.

Will

[Aladin Akyurek]

Originally Posted by WillR

Aladin,

Can I ask how your formulas deal with the fact that if D5 has no data, then a 1 should still be returned by the formula if C5's month is = 1 ? I admit your formulas are much cleaner than my effor, however I can't see the C5 evaluation.

Will

Getting sloppy I guess. I didn't see the C5 bit...

My point was that we need to include a non-empty test regarding cells expected to have a date. As I pointed out, it's a weak test, but better than none.

[WillR]

I totally agree with your approach... much cleaner then mine.. I just thought I was going a bit mad over the C5 bit (that maybe you'd discovered some form of "cloaking" device in a formula)..

Thanks for pointing it out