If formula with nested AND, OR, NOT ISBLANK within

Jessica Abbey

Not sure what I did wrong as when I'm reading through the formulat i wrote it seems correct, but I'm not getting the correct color value.

Essentially I need: if the status is "Not Started" OR "In Process" OR blank AND the "End Date" is not blank OR is less than today's date, turn red. If the "End Date" is blank=Gray", otherwise Green.

It does exactly what I need except when both "status" & "end date" are blank, I'm getting red as the output and I want gray. What am I missing?

=IF(AND(OR(Status@row = "Not Started", Status@row = "In Process", Status@row = ""), OR([End Date]@row < TODAY(), NOT(ISBLANK([End Date]@row)))), "Red", IF([End Date]@row = "", "Gray", "Green"))

Jessica Abbey

Is end date cell was populated but the status was not, I am getting incorrect argument. But seeing your formula helped me figure out what to do! Thanks!

Final Formula:

=IF(ISBLANK([End Date]@row), "Gray", IF(OR(AND(OR([End Date]@row < TODAY(), [End Date]@row = TODAY()), Status@row = "Complete"), [End Date]@row > TODAY()), "Green", IF([End Date]@row = TODAY(), "Red")))

Mark Cronk

Hi,

Try this:

=IF(ISBLANK([end date]@row), "Gray", IF(AND(OR(Status@row = "Not Started", Status@row = "In Process", ISBLANK(Status@row)), OR([End Date]@row < TODAY(), NOT(ISBLANK([End Date]@row))), "Red", "Green"))

Mark

Jessica Abbey

Is end date cell was populated but the status was not, I am getting incorrect argument. But seeing your formula helped me figure out what to do! Thanks!

Final Formula:

=IF(ISBLANK([End Date]@row), "Gray", IF(OR(AND(OR([End Date]@row < TODAY(), [End Date]@row = TODAY()), Status@row = "Complete"), [End Date]@row > TODAY()), "Green", IF([End Date]@row = TODAY(), "Red")))