Help turning tasks yellow less than 30 days till finish

I have the R/G ok, blue for complete, but I cant seem to get it yellow if the finish date is less than 30 days away:

=IF(Status@row = "complete", "Blue", IF(Finish@row < TODAY(), "Red", IF(Status@row = "In Progress", "Green")))

Tags:

Answers

  • PWNA Sam Harwart
    PWNA Sam Harwart âś­âś­âś­âś­âś­

    Hi @Paul Whelan !

    I don't see anything in the formula for Yellow. It also seems like you're using a mix of status and date values - you'll need to think through the precedence of these assignments.

    Blue = status = complete

    Red = finish < today [aka behind assigned date]

    Yellow = finish - today < 30

    Green = in progress [anything left over]

    Try something like this:

    =IF([Status3]@row = "Complete", "Blue", IF([Finish Date]@row < TODAY(), "Red", IF([Finish Date]@row - TODAY() < 30, "Yellow", "Green")))

  • =IF(Status@row = "Complete", "Blue", IF(Finish@row < TODAY(), "Red", IF(Finish@row - TODAY() < 30, "Yellow", "Green")))

    Perfect :) Thank you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!