RYG status on "Start Date" and "End Date"
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
Answers
-
Try this:
=IF([End Date]@row<= TODAY(-3), "Red", IF([End Date]@row>= TODAY(1), "Yellow", "Green"))
-
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!
-
Where do we enter the above formula in the sheet?
-
@Jostna Ransing You would enter it into a column set up for RYG symbols.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!