Sticky If statments

[craig80]

Afternoon,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
This one has been giving me nightmares, (Literary) all month.<o></o>
<o></o>
It is an IF statement which users ranges and arguments. I have similar, which works without problems. <o></o>
<o></o>
My main problem is that the arguments are not working in sequence. <o></o>
<o></o>
Any help would be fantastic.<o></o>
<o></o>
=<o></o>
IF(AND(OR(Item="X", Item="Y"),GOS="Failed",FAULTFail=""),"Failure",<o></o>
IF(AND(OR(Item="X", Item="Y"),GOS ="Incomplete",FAULTFail="", FaultDueDate<=TODAY()),"2. PM Must confirm Actual Date",<o></o>
IF(AND(OR(Item="X", Item="Y"),GOS ="Complete"),"7.Pass",<o></o>
IF(AND(OR(Item="X", Item="Y"),GOS ="Successful"),"7.Pass",<o></o>
IF(OR(Item<>"X", Item<>"Y, GType<>""),"8.Standard NA",<o></o>
IF(FaultDueDate=TODAY(),"3.Standard Due Today",<o></o>
IF(FaultDueDate<=TODAY()+3,"4.Three Days Warning",<o></o>
IF(FaultDueDate<=TODAY()+10,"5.Ten Day Plus",<o></o>
IF(FaultDueDate<=TODAY()+15,"6.Fifteen Day Plus",<o></o>
"9. Greater than Fifteen Days till standard")))))))))<o></o>
<o> </o>
By my thinking, if all of the first 5 Ifs are “False”, then it should move to 6, then 7 and so on. but it seems to jump to 7?

L<o></o>
<o> </o>

 

[gsbelbin]

Are you sure that FaultDueDate only holds a date and not a date and time?

 

[craig80]

Nope, defiantly a date field.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Thanks<o></o>
Craig<o></o>

 

[gsbelbin]

Have you tried using the Evaluate Formula tool under Tools, Formula Auditing? That will let you see how it is evaluating each part of the IF.

Also when you have a set of values which you believe should use the 6th IF, try entering = FaultDueDate = Today() and see if it evaluates to TRUE.