RYG status on "Start Date" and "End Date"

Thebomtaker

I have some thresholds to automate specifically on the "End Date"

My manager wants Red, Yellow, and Green status for specifically around the end date on a project. The projects are either 8 or 10 days for completion depending on the date range from start to end.

Red: This status will be applied 3 days after the "End Date" either via symbol column or color formatting by cell or font.

Yellow: This status will be applied 1 day before the "End Date" and would be overridden by the Red status when it is triggered.

Green: This status will apply before Yellow is triggered.

This sheet not only has the Start, and End Date but also has a completion date. The start and end date I am working with is for on-site installation work to be completed, then there are some other date fields that apply after the installation work until the final completion date for the project is entered.

I've looked at conditional formatting and have not found any out of the box options that seem to offer the level of detail my manager is looking for and I've tried some workflow automation to see if I can update a field, however nothing seems to work exactly right. I am now working on a formula, but I so far have not been very successful. Can anyone offer any insight on how this could be accomplished? Thanks

Paul Newcome

Try this:

=IF([End Date]@row<= TODAY(-3), "Red", IF([End Date]@row>= TODAY(1), "Yellow", "Green"))

Thebomtaker

Thank you very much. I was making it far too complicated and it was driving me nuts. This worked perfectly after a bit of tweaking. Thanks again!

Jostna Ransing

Where do we enter the above formula in the sheet?

Paul Newcome

@Jostna Ransing You would enter it into a column set up for RYG symbols.