Need some assistance on using "IF(AND) logic. I'm trying to render a "Red" "Yellow" or "Green".

I'm trying to render a "Red" "Yellow" or Green" solution from more than one criterial.  Formula reads this way.

=IF(AND([% Complete]25 < "1", [End Date]25 > TODAY()), "Red", IF(AND([% Complete]25 < "1", [End Date]25 = TODAY()), "Yellow"), IF(AND([% Complete]25 = "1", [End Date]25 = TODAY()), "Green"))

Essentially I want to reference the "% Complete" column.  If the completion is less than 1 (percentage format) then read the "end date" column.  If the "end date" date is before the current date "TODAY()" then select "Red".  If the "end date" date is = to the current date "TODAY()" then "Yellow".  If the "end date" date is before the current date "TODAY()", then "Green"

In other words, if the task is 100% complete then it stays "Green" regardless.  If the task is less than 100% complete then read the "end date" column to see if the task still has time to be completed (Green), is at risk (TODAY() = "end date"), (Yellow), if the current date has passed the "end date" and less than 100% complete, then (Red).

Best Answer


  • You don't need the parenthesis around the 1 and it might be better to use NETDAYS() to compare the current date to the end date. Try this:

    =IF(OR([% Complete]@row=1, NETDAYS(TODAY(), [End Date]@row)>1), "Green", IF(NETDAYS(TODAY(), [End Date]@row)<0, "Red", "Yellow"))

    Note: If TODAY() and [End Date] are the same, NETDAYS() returns 1 and, if TODAY() is after [End Date], a negative value is returned from NETDAYS().

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Another shorter option without using NETDAYS (as I am unsure what the benefit of that would be):

    =IF(OR([% Complete]@row = 1, [End Date]@row > TODAY()), "Green", IF([End Date]@row = TODAY(), "Yellow", "Red"))

  • I only suggested NETDAYS() because I know that it works from previous experience and I wasn't familiar with using logics with dates. I'm glad that I learned something new today. Thanks, Paul!

  • Thanks for the feedback folks!  I ended up finding my syntax error just minutes after I sent the request.  Sometimes we get in tunnel vision mode when working through things like this.  I appreciate the suggestions.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!