RYG ball based on due dates

rmpedro68986

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

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

eric.o

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