Formula for RYGB Status
Hi, I'm currently using this formula for status health but it's not the best because "Not Started" status is not showing Red if the End Date for the task is in the past. Obviously, the task is overdue even if it hasn't started. Can anyone assist to make this formula better?
=IF(Status@row = "Complete", "Blue", IF(Status@row = "Canceled", "", IF(Status@row = "Not Started", "", IF(ISBLANK([End Date]@row), "Blue", IF([End Date]@row >= TODAY(), "Green", IF([End Date]@row >= TODAY() - 3, "Yellow", "Red"))))))
Best Answers
-
Here it is in the same order as the criteria grid:
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
I just kept working the problem. Using helper columns to handle some of the functions outside of your main formula is key to making a lot of more complex things work in Smartsheet. Writing out a list of all possible combinations of your criteria really helps with getting a good idea of the right order for your nested IF statements. This trick of combining two disparate criteria (date and text) into one alphanumeric value is one I've used on quite a few Smartsheet projects.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
The reason this is happening is because as the system is evaluating your IFs, when it finds the status = Not Started, it applies the positive condition for that nested IF --blank-- and stops evaluating. Since you're wanting to evaluate on two criteria columns (Status and End Date), using some ORs and ANDs might help, but you have to figure out which criteria are most important and put them first.
=IF(OR(Status@row = "Complete", ISBLANK([End Date]@row), "Blue", IF(Status@row = "Canceled", "", IF([End Date]@row >= TODAY(), "Green", IF(AND([End Date]@row >= TODAY(-3), Status@row <> "Complete"), "Yellow", IF(OR(Status@row = "Not Started", [End Date]@row < TODAY(-3), "Red")))))
If the Status is Complete or End Date is blank, make it blue;
otherwise, If the Status is Canceled, make it blank;
otherwise, if the End Date is today or after today, make it green;
otherwise, if the End Date is equal to or after three days ago (but not today or after today), and the status is NOT Complete, make it yellow;
otherwise, if the status is Not Started or the End Date is earlier than 3 days ago, make it red.
Note: Use the Smartsheet parentheses color coding to make sure I have the right number of them at the end there!
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
@Jeff Reisman Thank you Jeff for the explanation! I used your formula and I get a #Incorrect Argument Set. I'm thinking there's a missing ) or (. It looks like there are enough parentheses at the end though. I'm not proficient at this so your help is appreciated.
-
Looks like I'm missing one right near the end, after TODAY(-3)!
[End Date]@row < TODAY(-3)), "Red")))))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
@Jeff Reisman I was so hoping this would be the resolution. It doesn't seem to turn red for those items which are "Not Started" and overdue. Also, I don't have anything for "In Progress" though that's my fault I didn't state I needed that in the beginning. Thanks again!
-
@Jeff Reisman I messed up in last response - actually the formula still shows #Incorrect Argument Set or #Unparseable, depending on how many parentheses at the end - I've tried adding and taking away. I don't know what's wrong.
-
You might have to just play around with the order or things in your logic.
Since you are using a lot of criteria, you could also create a "Helper" column to combine a text value representing the end date criteria with the status:
=IF([End Date]@row >= TODAY(), "ONT", IF([End Date]@row >= TODAY(-3), "DUE", IF(ISBLANK([End Date]@row), "NON", IF([End Date]@row < TODAY(-3), "LAT")))) + " " + Status@row
Then, based on your logic, you lay out a grid with what color each End Date/Status combo should be, allowing you to group results together in your big IF formula. (I added "Canceled" in here for anything with a status of Canceled, to differentiate those from rows with no end date.)
ONT Complete = Blue
ONT In Progress = Green
ONT Not Started = Green
ONT Canceled = Canceled
DUE Complete = Blue
DUE In Progress = Yellow
DUE Not Started = Yellow
DUE Canceled = Canceled
LAT Complete = Blue
LAT In Progress = Red
LAT Not Started = Red
LAT Canceled = Canceled
NON Complete = Blue
NON In Progress = ""
NON Not Started = ""
NON Canceled = Canceled
=IF(Status@row = "Complete", "Blue", IF(OR(Helper@row = "ONT In Progress", Helper@row = "ONT Not Started"), "Green", IF(OR(Helper@row = "DUE In Progress", Helper@row = "DUE Not Started"), "Yellow", IF(OR(Helper@row = "LAT In Progress", Helper@row = "LAT Not Started"), "Red", IF(OR(Helper@row = "NON In Progress", Helper@row = "NON Not Started"), "", IF(Status@row = "Canceled", "Canceled"))))))
I think the order makes sense here:
Anything with status of complete is blue,
On time AND in progress or on time AND not started is green,
Due in the past three days AND in progress or Due in the past three days AND not started is yellow,
Late AND in progress or Late AND not started is red,
No end date AND in progress or no end date AND not started is blank,
Anything with status of canceled is Canceled.
All this is tested and working:
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Here it is in the same order as the criteria grid:
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Oh my gosh. I don't know what you did, but it works! Thank you, thank you! This is gold.
-
I just kept working the problem. Using helper columns to handle some of the functions outside of your main formula is key to making a lot of more complex things work in Smartsheet. Writing out a list of all possible combinations of your criteria really helps with getting a good idea of the right order for your nested IF statements. This trick of combining two disparate criteria (date and text) into one alphanumeric value is one I've used on quite a few Smartsheet projects.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 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!