Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

To set RYG using a formula that considers Todays date and Completion %

edited 12/09/19 in Archived 2016 Posts

I would like to set the risk RYG status column colour based on set conditions.


IF % complete = 0 And 'start' is todays date to show Risk 


IF % complete = between 90 & 100 to show Green 


somethign like this but it doesn't work 


=IF(AND([%  Complete]row# = "0", [Start]165="TODAY", "Red"))


Any ideas?


  • Matt Hines

    I think there are a few things.


    Instead of this:

    =IF(AND([%  Complete]row# = "0", [Start]165="TODAY", "Red"))


    Try this:

    =IF(AND([%  Complete]165 = 0, [Start]165=TODAY()), "Red", "Yellow")


    I think that should work.

  • DRodellar

    A more complete formula on row 2, with column RYGG balls, including:

    - Status (Complete, Not Started, In Progress) 

    - Start Date

    - End Date

    - % Complete


    =IF(OR(Status2 = "Not Started", [Start Date]2 > TODAY()), "Gray", IF(AND([% Complete]2 < 0.8, [End Date]2 < TODAY()), "Red", IF(Status2 = "Complete", "Green", "Yellow")))


This discussion has been closed.