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!