RYG Change Based on Moving Variable

Michele.G.

Hi, all. I was looking to write a formula that would change a RYG based on if the project was still on track for the determined amount of time. It would start green and if the project was projecting as running longer than anticipated would change to yellow and then red when it went over a further threshold(of say %7 of the net days allotted). I wanted it to be based off of a user inputting their best guess of a percentage that the project is complete. So logically I thought I would take the NETWORTHDAYS(TODAY(), [Due Date]) and compare that to NETWORTHDAYS([Start Date],[End Date]) * [User Guess] and I'm having no luck. Any opinions on my code? Thank you much for taking the time!

 

=IF(NETWORKDAYS(TODAY(), [End Date]1) > NETWORKDAYS([Start Date]1, [End Date]1) * [Guess]1, "Green", IF(NETWORKDAYS(TODAY(), [End Date]1) < NETWORKDAYS([Start Date]1, [End Date]1) * [Guess]1, "Yellow", IF(NETWORKDAYS(TODAY(), [End Date]1) < NETWORKDAYS([Start Date]1, [End Date]1 + NETWORKDAYS([Start Date]1, [End Date]1 * %7, "Red")))

Chris McKay

Hi Michele,

Not sure if this is exactly what you're after:

1. In the RYG Status column
   
   =IF(Elapsed1 <= Guess1, "Green", IF(AND(Elapsed1 >= 1.07, Guess1 < 1), "Red", "Yellow"))
2. In a new Elapsed column
   
   =NETWORKDAYS([Start Date]1, TODAY()) / NETWORKDAYS([Start Date]1, [End Date]1)

You can do away with the Elapsed column, but I always like to have it handy to simplify the Status column and drive notifications & alerts etc.

This will show:

1. Green if Elapsed is less than Guess
2. Yellow if Elapsed is greater than Guess but less than 107%  (of net allocated days)
3. Red if Elapsed is 107% (of net allocated days) and Guess is less than 100%

As an FYI, Smartsheet does not treat percentages literally. 7% of a value is actually represented as 0.07, so you can see what the formula above is doing (i.e. 1.07 = 107%).

Hope this helps.

Kind regards,

Chris McKay