RYG ball based on due dates

I already have a formula created that automates the RYG ball based on due dates...

=IF(Complete1 = 1, "Gray", IF([Due Date]1 < TODAY(+3), "Red", IF(AND([Due Date]1 >= TODAY(+3), [Due Date]1 < TODAY(+7)), "Yellow", IF([Due Date]1 >= TODAY(+7), "Green", "Nope"))))

 

What im having an issue with is whenever the cell in the due date column is blank, the RYG ball automates to red. I would like to automate it to Gray whenever the cell block is empty but everytime i plug the IF(ISBLANK([Due Date]1), "Gray" it doesn't do what i need it to. If i put it in the beginning it doesn't allow me to have a date before today (Like if I need to back date something), at the end it does nothing and anywhere else makes it "unparsable" or "invalid". 



here is a screenshot of the sheet

smartsheet RYG Ball.png

Comments

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi,

    Try changing the first part of the formula with this.

    =IF(OR(Complete1 = 1; Complete1 = ""); "Gray";   

    The same version but with the below changes for your and others convenience.    

    =IF(OR(Complete1= 1, Complete1 = ""), "Gray",

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    Did it work?

    Have a fantastic week & Happy Holidays!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • eric.o
    eric.o Employee

    Hello,

    Happy to help! If you desire to have Status turn grey if the [Due Date] is blank you can achieve this by placing the ISBLANK Function at the beginning of the formula. 

    I've successfully achieved your desired goal utilizing this formula:

     

    =IF(ISBLANK([Due Date]@row), "Gray", IF(Complete@row = 1, "Gray", IF([Due Date]@row < TODAY(+3), "Red", IF(AND([Due Date]@row >= TODAY(+3), [Due Date]@row < TODAY(+7)), "Yellow", IF([Due Date]@row >= TODAY(+7), "Green", "Nope")))))

     

    Please let us know if you have any questions on the above.

    Cheers, 

    Eric  

    Smartsheet Support

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!