RYG balls & Dates

Leslye Jackson

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

Paul Newcome

Try something like this...

=IF([Start Date]@row = "", "Gray", IF([Complete Date]@row < [Due Date]@row, "Red", IF([Complete Date]@row > [Due Date]@row, "Green", "Yellow")))

Paul Newcome

Remove the actual row number. You want to use either 21 OR @row. Not both.

Paul Newcome

The formula should work. Try removing the space from between the quotes so it is "" instead of " ". It shouldn't make a difference, but it is at least worth a try.

Paul Newcome

Which dates would you want to reference being blank to keep the cell blank? Right now we have if the Start Date is blank to output a Gray ball.

Paul Newcome

What are your requirements for each color?

Leslye Jackson

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

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

[Start Date]21 Blank =Gray

Thanks Paul!

Paul Newcome

Try something like this...

=IF([Start Date]@row = "", "Gray", IF([Complete Date]@row < [Due Date]@row, "Red", IF([Complete Date]@row > [Due Date]@row, "Green", "Yellow")))

Leslye Jackson

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

Paul Newcome

Remove the actual row number. You want to use either 21 OR @row. Not both.

Leslye Jackson

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 ?

Paul Newcome

The formula should work. Try removing the space from between the quotes so it is "" instead of " ". It shouldn't make a difference, but it is at least worth a try.

Leslye Jackson

It worked !!! Thank so much Paul!!

Paul Newcome

Great! Happy to help. 👍️

Leslye Jackson

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?

Paul Newcome

Which dates would you want to reference being blank to keep the cell blank? Right now we have if the Start Date is blank to output a Gray ball.

Leslye Jackson

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

Leslye Jackson

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!!!