IF/AND Statement

MOO

Hello,

I have been attempting to use the IF/AND statement with my status balls with no luck. Here are my conditions:

Green = Outreach Date <15days & Go-live is blank

Yellow= Outreach Date > 15 days & Go-live is blank

Red = Outreach Date > 30 days & Go-live is <Today 

I am needing them all in one formula for my status ball columns (Green, Yellow, Red. I am not sure what I have been doing wrong. Help is greatly appreciated.

KPH

Without seeing your formula, I can't help with what you are doing wrong, but this formula should do what you want. It will also turn green for any blank days.

=IF(AND([Outreach Date]@row < TODAY(15), ISBLANK([Go-Live]@row)), "Green",

IF(AND([Outreach Date]@row > TODAY(15), ISBLANK([Go-Live]@row)), "Yellow",

IF(AND([Outreach Date]@row > TODAY(30), [Go-Live]@row < TODAY()), "Red"

)))

If you want to stop that, another IF can be added at the very start to return nothing if Outreach Date is blank.

=IF(ISBLANK([Outreach Date]@row), "", IF(AND([Outreach Date]@row < TODAY(15), ISBLANK([Go-Live]@row)), "Green", IF(AND([Outreach Date]@row > TODAY(15), ISBLANK([Go-Live]@row)), "Yellow", IF(AND([Outreach Date]@row > TODAY(30), [Go-Live]@row < TODAY()), "Red"))))

KPH

Without seeing your formula, I can't help with what you are doing wrong, but this formula should do what you want. It will also turn green for any blank days.

=IF(AND([Outreach Date]@row < TODAY(15), ISBLANK([Go-Live]@row)), "Green",

IF(AND([Outreach Date]@row > TODAY(15), ISBLANK([Go-Live]@row)), "Yellow",

IF(AND([Outreach Date]@row > TODAY(30), [Go-Live]@row < TODAY()), "Red"

)))

If you want to stop that, another IF can be added at the very start to return nothing if Outreach Date is blank.

=IF(ISBLANK([Outreach Date]@row), "", IF(AND([Outreach Date]@row < TODAY(15), ISBLANK([Go-Live]@row)), "Green", IF(AND([Outreach Date]@row > TODAY(15), ISBLANK([Go-Live]@row)), "Yellow", IF(AND([Outreach Date]@row > TODAY(30), [Go-Live]@row < TODAY()), "Red"))))

MOO

Thank you so much! it looks like I was missing the double brackets right before the color.

KPH

Glad I could help.

When you enter the formula in smartsheet it color codes the brackets in pairs so keep an eye on those. The two before the color are to close the ISBLANK function and to close the AND function, both of those are closed, before the comma that takes you to the next part of the IF, where you enter the output.