# RYG balls & Dates

Options
edited 07/16/20

Hi All,

I am working on constructing a formula that will automate based on the dates (Start Date, Due Date and Complete Date) I can get the balls to automate using one color or another but not all four within the same formula. I keep getting an unparseable or incorrect argument error. The below formula actually works however it wont allow me to do another If statement for the Green & Yellow balls. Any feedback is very much appreciated.

=IF([Complete Date]20 > [Due Date]20, "Red", IF([Complete Date]20, " ", "Gray"))

• ✭✭✭✭✭✭
Options

What are your requirements for each color?

• Options

Complete Date]21<[Due Date]21=Red, Yellow(if false)

Complete Date]21>[Due Date]21=Green

[Start Date]21 Blank =Gray

Thanks Paul!

• Options

I noticed that @row was not in color while writing... is that normal?

• Options

Paul you are awesome that did work but I notice that when the cell is blank it does not indicate so with the grey ball should I use Isblank in the beginning ?

• Options

It worked !!! Thank so much Paul!!

• ✭✭✭✭✭✭
Options

Great! Happy to help. 👍️

• Options

Hey Paul working with this same sheet : after designating a column to symbols (RYGG balls) can you create a formula that would display no balls if no dates are entered?

• Options

We want to to have the balls not present if no date is is entered. I found the below formula to work but I want to thank you for your quick response and transparent answers. You rock Paul!!!!

=IF(ISBLANK([Due Date]@row), "", IF([Complete Date]@row < [Due Date]@row, "Green", "Red"))

• Options

Good afternoon Paul,

I hope its OK that I am reaching out here, however I have a question about this sheet but didn't know if I should start a whole new thread or no...so here goes.

The dependencies functionality that allows automatic calculations of % complete as well as updates the date columns automatically. As we build this sheet out we want to possibly be able to narrow down dependencies at a task level. I only see the option to do it for the entire sheet however unfortunately not every task in our project would need the dependency capability.

I disabled the capability because I found that with it on once the due was entered it updated the completed date that may be inauthentic to the client. We want to use the % complete to monitor the project but we don't need each task to have the same functionality. Any advice you can give would be golden. I am also ok with an article or video that could point me in the right direction!

Thanks a bunch Paul!!!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!