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

Find more posts tagged with

Formulas

Project Management

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…