How to automate RYGB balls based on due date and completion

Sara Downing

I am trying to automate the "Status" column to display the following based on the "Original Action Due Date" column:

Coming due in greater than 60 days - Green

Coming due in 60 days - Yellow

Past due - Red

I am also trying to automate the "Status" column to return a blue color when the "Date Closed" column is populated with a date.

I appreciate any help!

Bill Brendle

You could use something like this changing the color scheme

the cells in the "Duration" column has this formula =NETDAYS($StartDate2, TODAY()

=IF(Duration19 <= 10, "Green", IF(Duration19 <= 15, "Yellow", IF(Duration19 <= 19, "Blue", IF(Duration19 >= 20, "Red"))))

Scott Nuss

Can you help me modify this formula to less than 60 days? What part of the formula would I modify for less days. Thank you.

Paul Newcome

@Sara Downing I would suggest using something like this to remove the need for the additional column:

=IF([Original Action Due Date]@row > TODAY(60), "Green", IF([Original Action Due Date]@row >= TODAY(), "Yellow", "Red"))

@Scott Nuss Does the above help, or do you need something different?