If combined with OR & AND

[Micki]

Cell E14 contains a date and I want certain things to happen if the date is
equal to certain criteria. I'm having problems with the syntax.
IF(E14>="1/31/2008",0,if(AND(e14<"1/31/2008",e14>"1/14/2007",L14/12,if(AND(e14<"1/15/2008",e14>"12/31/2007",L14/24)))))

Thank you.

 

[David Biddulph]

You are comparing with text strings, not with dates. For dates, use the
DATE() function.

 

[T. Valko]

Try expressing the dates like this:

=IF(E14>=--"1/31/2008"

Personally, I hate using those kinds of expressions. They're ambiguous and
cryptic. I prefer to use the DATE function like this:

=IF(E14>=DATE(2008,1,31)

This can make the formula longer but there is no misunderstanding of what
I'm comparing!

Even better, use cells to hold the dates if you can:

A1 = 1/31/2008

=IF(E14>=A1

 

[pinmaster]

Hi,

Every AND needs an Open parenthyses and a close parenthyses, and needs to be
treated as a single condition, an example below:

IF(AND(A1=2,B1=3),"YES","NO")

You put all your closing parenthyses at the end of the formula, close your
AND's and you should be ok.

HTH
Jean-Guy

 

[Micki]

Was hoping I wouldn't have to. Thanks.

You are comparing with text strings, not with dates. For dates, use the
DATE() function.

 

[Elkar]

First of all, if E14 contains a date, then you can't compare it to a text
string. You'll need to use the DATE function. Second, the AND functions are
redundant, since they're only evaluated in the FALSE portion of your IF
statements.

Try this:

=IF(E14>=DATE(2008,1,31),0,IF(E14>DATE(2008,1,14),L14/12,IF(E14>DATE(2007,12,31),L14/24)))

Note that dates in 2007 or older will return a FALSE value since you didn't
specify what to do in that event.

HTH,
Elkar

 

[T. Valko]

Well, between the 3 of us, myself, David and Pinmaster we "collectively"
came up with the correct answer but invidually, we all missed something!

Pinmaster got the missing ")" for the AND functions but missed the TEXT
dates. David and myself got the TEXT dates but missed the ")" for the AND
functions. So, putting it all together correctly:

=IF(E14>=--"1/31/2008",0,if(AND(e14<--"1/31/2008",e14>--"1/14/2007"),L14/12,if(AND(e14<--"1/15/2008",e14>--"12/31/2007"),L14/24)))

 

[Farhad]

--
Farhad Hodjat


IF(E14>="1/31/2008",0,IF(AND(e14<"1/31/2008",e14>"1/14/2007"),L14/12,IF(AND(e14<"1/15/2008",e14>"12/31/2007"),L14/24,"")))

i think this should be the correct syntax just put what you want instead ""
that i put in your if chains.

Thanks,

 

[Micki]

I added something to my formula and now am getting a return of "FALSE". I
need the return to be zero or blank. Cell QW8 ias equal to " " when I get the
FALSE return. I tried making it a zero instead of blank, and get the same
return.

=IF(OR(A8="New
Hire",A8="Attrition",A8="Transfer",0),IF(Q8="",0),IF(E8>=--"1/31/2008",0,IF(AND(E8<--"1/31/2008",E8>--"1/14/2007"),L8/12,IF(AND(E8<--"1/15/2008",E8>--"12/31/2007"),L8/24))))

 

[T. Valko]

Try this:

=IF(OR(A8={"New Hire","Attrition","Transfer"}),0,IF(Q8="",0,
IF(E8>=--"1/31/2008",0,IF(AND(E8<--"1/31/2008",E8>--"1/14/2007"),L8/12,
IF(AND(E8<--"1/15/2008",E8>--"12/31/2007"),L8/24,"")))))

 

[Micki]

Perfect! Thank you.

Try this:

=IF(OR(A8={"New Hire","Attrition","Transfer"}),0,IF(Q8="",0,
IF(E8>=--"1/31/2008",0,IF(AND(E8<--"1/31/2008",E8>--"1/14/2007"),L8/12,
IF(AND(E8<--"1/15/2008",E8>--"12/31/2007"),L8/24,"")))))

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Perfect! Thank you.