RYG balls based on dates of activities completed

Debra Olson

I have been following some threads discussing how to use RYG balls in conditional formulae based on a date column (i.e. green if the date is in the future, yellow if it is today, red if in the past, etc.), and those are very helpful!  I would like to do the following, however, and I cannot find a thread that addresses this in particular:

 

I have a column for a date on which a report is due (Final Report Due), and another for the date on which a report was actually submitted (Final Report Submitted).  There is also a third column for Project End Date.  

 

I would like my Project Status ball (RYG) to reflect green if the date of the Project End Date is in the future, yellow if the Project End Date is in the past AND the Final Report Due is in the future, and red if the Project End Date is in the past and the Final Report Submitted is empty.

 

Is this possible?  Is there an alternative I could achieve by modifying things?

Greg Gates

You can definitely do that! In writing this formula I made one assumption - If the Project End Date is in the Past and the Final Report Due is in the past, and you have a Final Report Submitted the color should go back to green. If that's ok, putting this formula in your Status column should do the trick!

 

=IF([Project End Date]1 > TODAY(), "Green", IF([Final Report Due]1 > TODAY(), "Yellow", IF(ISBLANK([Final Report Submitted]1), "Red", "Green")))

 

Basically, the IF-statements go through three steps:

Step 1) If the project end date is in the future, display the "Green" ball. Otherwise, go to step 2.

Step 2) If the final report due date is in the future, display a "Yellow" ball. Otherwise, go to step 3.

Step 3) If the final report due date has passed and a final report wasn't submitted, display a "Red" ball. Otherwise, display a "Green" ball because a report has been submitted.

 

Let me know if that solves the problem you outlined. If I misunderstood something, we can easily adapt this solution too!

Debra Olson

@GregGates, you are awesome!  Thank you so much for your help!  I have been struggling with this for so long, and this solved it perfectly.

 

The only hangup is that I had the formatting set so that when the project is closed (defined as typing CLOSED in another column (the Comments), then everything is greyed out in the row and the ball turns grey.  When the formula you gave me is used, it seems to override this and the ball stays green.  Is there an incompatibility between the formula and the formatting?  I'm perfectly happy to manually change it to a grey ball when I close the project, but let me know if I'm overlooking something.  

 

You are awesome!  Thank you again!

Greg Gates

You could totally do it! Just add another IF-statement to the very beginning of our chain (since CLOSED is the most important attribute)

 

=IF(Comments1 = "CLOSED", "Gray", IF([Project End Date]1 > TODAY(), "Green", IF([Final Report Due]1 > TODAY(), "Yellow", IF(ISBLANK([Final Report Submitted]1), "Red", "Green"))))

Debra Olson

Oh, that makes sense!  Wonderful!  Thank you!