RAG Status formula scenario
Hi Smartsheets wizards,
Good Evening!
I have created a formula that has been working great, however I find that if the actual finish date is missing for any reason the RAG does not change to RED. Here is the formula and the screenshot of the schedule. Kindly advise what I should change to get the RAG to change to red if the actual date is missing for any reason. Look at the date 8th Feb 2023 row.
=IF(OR(AND([Trending Start]@row < TODAY(), [Actual Start]@row = ""), AND(([Actual Start]@row - [Trending Start]@row) >= 20, [% Complete]@row <= 0.1)), "Red", IF(OR(AND(([Actual Start]@row - [Trending Start]@row) >= 10, [% Complete]@row < 0.5), AND([Actual Finish]@row > [Trending Finish]@row, [% Complete]@row <= 0.5)), "Yellow", IF(AND([% Complete]@row = 1, [Actual Finish]@row <> ""), "Blue", "Green")))
Answers
-
Hi @Avinash Chandra,
Here's what I've interpreted from your formula:
Red
trending start is in the past and there is no actual start date
less than 10% complete and actual start is greater than or equal to 20 days after trending start
Yellow
less than 50% complete and actual start is great than or equal to 10 days after trending start
less than 50% complete and actual finish is after trending finish
Blue
task is complete and actual finish is not blank
Otherwise Green
Could you confirm that you in fact want the health to be red in any row that is missing an Actual Finish date? If so, you could add that to the beginning of your formula.
-
What is happening: People are copying and pasting Trending start date into actual start date and missing out filling actual finish date. RAG remains Green. It should change to RED.
How do I add double condition to this: (([Actual Start]@row - [Trending Start]@row) >= 20, [% Complete]@row <= 0.1)), "Red", AND Actual finish date is blank.
Kindly advise.
-
Based on the formula you posted above, you want to add the condition as shown in bold italics below, correct?
Red
trending start is in the past and there is no actual start date
less than or equal to 10% complete, actual start is greater than or equal to 20 days after trending start, and Actual Finish is blank
Yellow
less than 50% complete and actual start is great than or equal to 10 days after trending start
less than 50% complete and actual finish is after trending finish
Blue
task is complete and actual finish is not blank
Otherwise Green
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!