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 %

Options
MWatson
MWatson
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?

Comments

  • Matt Hines
    Options

    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
    Options

    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.