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")))