Automated RYG ball issues

Kelly Ospina

I have a sheet where I am using red, yellow, green and gray balls to indicate project health. 

If the project is complete, the ball is gray. If the due-date is within the next 14 days, yellow. If due-date is today or in the past, red, and everything else green. 

I must have something wrong somewhere, because the red isn't showing up as expected. 

Here is a link to my sheet: https://app.smartsheet.com/b/publish?EQBCT=562b74cdaf124ac8b252a717caf3a170

And here is the formula: 

=IF(ISDATE([Project Completion Date]1), "Gray", IF(TODAY() >= ([Project Due Date]1 - 14), "Yellow", IF(TODAY() >= ([Project Due Date]1), "Red", "Green")))

Any help would be appreciated. Thank you!

L_123

you can't subtract a number from a date. Instead tell the program today(14) which is 14 days from today. to directly subtract you would have to use the date() formula, which is more effort than is needed.

=IF(ISDATE([Project Completion Date]1), "Gray", IF(TODAY(14) >= [Project Due Date]1, "Yellow", IF(TODAY() >= ([Project Due Date]1), "Red", "Green")))

 

Also if you use the @row reference it helps smartsheet run faster, which is especially useful if the sheet becomes really large. it's a good habit to get into.

=IF(ISDATE([Project Completion Date]@row), "Gray", IF(TODAY(14) >= [Project Due Date]@row, "Yellow", IF(TODAY() >= ([Project Due Date]@row), "Red", "Green")))

Kelly Ospina

Thank you, but I think something still isn't quite working. The red ball still doesn't appear, even when I adjust the due-date to be today or in the past.  

 

 

Kelly Ospina

Ah ha! I got it - It was the syntax. I re-arranged the order of the functions and now it works. Thank you so much!

Here is the final version for anyone who wants something similar: =IF(ISDATE([Project Completion Date]@row), "Gray", IF(TODAY() >= ([Project Due Date]@row), "Red", IF(TODAY(14) >= [Project Due Date]@row, "Yellow", "Green")))