Automated RYG ball issues

Kelly Ospina
Kelly Ospina ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

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:

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
    L_123 ✭✭✭✭✭✭
    edited 05/10/18

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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!