RYG Status formula question
Hello. I'm currently solving for this: If the status says Complete, then blue. If the status says At Risk, or In Progress then calculate the RYG status based on the following parameters:
- If the Target End date is 15 or more days from today, it's green.
- If the Target End date is 4-14 days from today, it's yellow.
- If the Target end date is equal to today, less than 3 days from today or in the past it's red.
This is the formula I'm using:
=IF(Status@row = "Complete", "Blue", IF(AND(OR(Status@row = "At Risk", Status@row = "In Progress"), [Target End Date]@row >= TODAY(15)), "Green", IF(AND(OR(Status@row = "At Risk", Status@row = "In Progress"), [Target End Date]@row >= TODAY(4)), "Yellow", IF(AND(OR(Status@row = "At Risk", Status@row = "In Progress"), [Target End Date]@row <= TODAY(3)), "Red", ""))))
I would like to add the following to the above formula:
- If the status is "On Hold" then yellow.
- If the status is "Not started" and the target start date is in the future, it is green. If the status is "Not Started" and the target start date has past, it's red.
Can anyone help me with adding that to the above formula, please?
Answers
-
Seven nested IF statements. That's a lot but should handle your conditions.
= IF(AND(Status@row = “Not started”, [Target Start Date] < TODAY()), “Red”, IF(AND(Status@row = “Not started”, [Target Start Date] >= TODAY()), “Green”, IF(Status@row = “On Hold”, “Yellow”, IF(Status@row = "Complete", "Blue", IF(AND(OR(Status@row = "At Risk", Status@row = "In Progress"), [Target End Date]@row >= TODAY(15)), "Green", IF(AND(OR(Status@row = "At Risk", Status@row = "In Progress"), [Target End Date]@row >= TODAY(4)), "Yellow", IF(AND(OR(Status@row = "At Risk", Status@row = "In Progress"), [Target End Date]@row <= TODAY(3)), "Red", "")))))))
Cheers,
John
-
@John Shane when I copy and paste the formula it says unparseable but I added the @row to the target start dates and it's saying invalid operation. any ideas?
-
Not quite sure what the problem is/was, but I just worked it out again in my test bed. I tried to copy/paste, but Smartsheet did not like that. I validated the formula below.
The formula below should match what you are trying to do.
=IF(AND(Status@row = "Not started", [Target Start Date]@row < TODAY()), "Red", IF(AND(Status@row = "Not started", [Target Start Date]@row >= TODAY()), "Green", IF(Status@row = "On Hold", "Yellow", IF(Status@row = "Complete", "Blue", IF(AND(OR(Status@row = "At Risk", Status@row = "In Progress"), [Target End Date]@row >= TODAY(15)), "Green", IF(AND(OR(Status@row = "At Risk", Status@row = "In Progress"), [Target End Date]@row >= TODAY(4)), "Yellow", IF(AND(OR(Status@row = "At Risk", Status@row = "In Progress"), [Target End Date]@row <= TODAY(3)), "Red", "")))))))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!