Complicated IF statement

[craig80]

Hi All,

I am trying to create a IF that has a nested AND. (i think)

The steps it needs to take are as follows...

1. checks GDueDate is >=TODAY()+10,"Ten Day Plus",
2. checks GDueDate is >=TODAY()+3,"Three Days Warning",
3. checks GDueDate is >TODAY(),"Standard Due Today",
4.a checks GDueDate is <=TODAY()
AND
4.b Check QMP<>"-","Potential Fail Needs confirming by PM",
5.Check QGOSFAILED="","Team Leader needs to confirm failure in SAP",""))))


This is where i am, but need to nest an AND


=IF(GDueDate="","",
IF(GDueDate<=TODAY()+10,"Ten Day Plus",
IF(GDueDate<=TODAY()+3,"Three Days Warning",
IF(GDueDate<=TODAY(),"Standard Due Today",
IF(QMP<>"-","Potential Fail Needs confirming by PM",
IF(QGOSFAILED="","Team Leader needs to confirm failure in SAP",
""))))))


<TABLE style="WIDTH: 572pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=762 border=0><COLGROUP><COL style="WIDTH: 127pt; mso-width-source: userset; mso-width-alt: 6180" width=169><COL style="WIDTH: 146pt; mso-width-source: userset; mso-width-alt: 7094" width=194><COL style="WIDTH: 124pt; mso-width-source: userset; mso-width-alt: 6034" width=165><COL style="WIDTH: 143pt; mso-width-source: userset; mso-width-alt: 6985" width=191><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1572" width=43><TBODY><TR style="HEIGHT: 15pt" vAlign=bottom height=20><TD class=xl107 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 127pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: gray" width=169 bgColor=#808080 height=20>Guarantee Due Date [SOS]</TD><TD class=xl107 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 146pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: gray" width=194 bgColor=#808080>Terms to Customer Date [SOS]</TD><TD class=xl107 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 124pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: gray" width=165 bgColor=#808080>Quote GOS Failed? [SOS]</TD><TD class=xl101 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 143pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow" width=191 bgColor=#ffff00>Quote Jep Summary</TD><TD class=xl111 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 32pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #b2a1c7" width=43 bgColor=#b2a1c7>Q M P</TD></TR><TR style="HEIGHT: 15pt" vAlign=bottom height=20><TD class=xl113 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #ccffff" align=right bgColor=#ccffff height=20>26/07/2010</TD><TD class=xl113 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #ccffff" bgColor=#ccffff></TD><TD class=xl113 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #ccffff" bgColor=#ccffff></TD><TD class=xl102 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: red" bgColor=#ff0000>Ten Day Plus</TD><TD class=xl100 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow" bgColor=#ffff00>-</TD></TR><TR style="HEIGHT: 15pt" vAlign=bottom height=20><TD class=xl113 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #ccffff" align=right bgColor=#ccffff height=20>15/04/2010</TD><TD class=xl113 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #ccffff" align=right bgColor=#ccffff>18/04/2010</TD><TD class=xl113 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #ccffff" bgColor=#ccffff></TD><TD class=xl102 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: red" bgColor=#ff0000>Potential Fail Needs confirming</TD><TD class=xl100 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: yellow" align=right bgColor=#ffff00>1 </TD></TR></TBODY></TABLE>


Any Help greatly received.

Thanks

Craig

 

[Derek Brown]

First of all, the operators (e.g. <=) in the description seem to be reversed in the formula examples that you have given.
However, you need something like:
IF(AND(GDueDate<=TODAY(),QMP<>"-"),"Potential Fail Needs confirming by PM",
There is also an 'OR' function.

 

[craig80]

Thank you, thats exactly where I am going... really appreciate your help.

Craig