Help with formula with RYG Balls and Start/End Dates
Hello,
I am trying to clean up a program management smartsheet. I am wanting the status column (RYG balls) to communicate with the % complete column and the start and end date column.
This is my current formula:
=IF([% Complete]93 = 1, "Green", IF([% Complete]93 > 0.49, "Yellow", IF([% Complete]93 < 0.48, "Red")))
With this formula, I am running into issues. When a task has a start date in the future, the status ball is red. I am wanting to eliminate this issue because the task cannot be completed until the future start date. Also, some tasks have end dates that are past due and the RYG balls do not change red because they are talking to the %complete. Ex: Task A is 75% complete but 2 weeks past due = Yellow Circle.
Is it possible to have the formula do these things I am asking for? I may have to choose to only focus on % complete or start/end dates.
Answers
-
Paul Newcome ✭✭✭✭✭✭You can account for all of these things within the same formula. What are your exact criteria for each color? Once we get that part figured out, we can start working on building the formula.
-
Red is late/not complete (less then 49%)
Yellow is pending/half way complete
Green is done/complete 100%
I hope that is helpful
-
Paul Newcome ✭✭✭✭✭✭
A few questions...
"Red is late/not complete (less then 49%)"
Do you mean late OR less than 49%? What if it is less than 49% but not late? What if the Start Date is in the past, the End Date is in the future, but the % is 0? Does the Start Date being in the past and the task not being started yet count as "late"?
"Yellow is pending/half way complete"
What is "pending"? By "halfway" do you mean exactly 50% or do you mean 49% and greater?
"Green is done/complete 100%"
Is "done" determined by a checkbox? Is it date driven, or is the only way for it to be "Done" is when complete is 100%.
As much detail into every requirement as possible would really help. Here is what I suggest:
Create a list with each of the colors listed.
Create a list of every possible outcome but keep in mind that if the dates are the same and 1% is treated the same as 10%, then you can just say "1% - 10%". There's no need to duplicate it for 1%, 2%, 3%, 4%, etc if they are all the same result.
Then take each of those outcomes (as many different possibilities that you will want to differentiate between) and move them to under each of the colors.
Something like this...
Red:
% Complete is less than 100 AND Finish Date is in the past
% Complete is less than 50 AND Start date is in the past (even if Finish Date is in the future)
% Complete is 0 and Start Date is in the past
Yellow:
% Complete is greater than or equal to 50 and Finish Date is in the Future
%完成小于100,完成日期是托德AY
Green:
% Complete is 100
Of course if there are other possible combinations that need to be accounted for, you would put them in place. Once we get all of the detailed logic figured out we can start building out a formula for testing.
-
Great comments/questions! I like the ones you have written so I have just copied it below. But I do have an additional requirement: Is there any option for no color ball for items with a future start date and future end date? I would like there not to be a colored ball if the tasks is not being worked on at all because it cant be until a future date.
Red:
% Complete is less than 100 AND Finish Date is in the past
% Complete is less than 50 AND Start date is in the past (even if Finish Date is in the future)
% Complete is 0 and Start Date is in the past
Yellow:
% Complete is greater than or equal to 50 and Finish Date is in the Future
%完成小于100,完成日期是托德AY
Green:
% Complete is 100
-
Paul Newcome ✭✭✭✭✭✭
It is very possible. So lets break this down in formulas...
Blank:
IF([Start Date]@row > TODAY(), "")
Green:
IF([% Complete]@row = 1, "Green")
Red:
IF([Finish Date]@row < TODAY(), "Red")<-------We do not need to specify that it is less that 100% because to get to this point in the formula it would have to be. Nested IF's read left to right and stops at the first "true" value. So if it get's to this point, then it has already determined that it is less than 100%.
IF([% Complete]@row < .5, "Red")<-------Same thing as above. By getting to this point in the formula, we already know that the Start Date must either be today or in the past because if it was in the future, it would have stopped at the first "true" which generates a blank.
IF(AND([% Complete]@row = 0, [Start Date]@row < TODAY()), "Red")
which can be combined using an OR for each of the criteria to include nesting the AND inside of the OR...
IF(OR([Finish Date]@row < TODAY(), [% Complete]@row < .5, AND([% Complete]@row = 0, [Start Date]@row < TODAY())), "Red")
Yellow:
IF([Finish Date]@row = TODAY(), "Yellow")<-------Same as the second "Red". We already established that it would have to be less than 100% to make it this far into the formula.
IF([% Complete]@row >= .5, "Yellow")
which can be combined using an OR for each of the criteria
IF(OR([Finish Date]@row = TODAY(), [% Complete]@row >= .5), "Yellow")
.
Now we can put it all together...
=IF([Start Date]@row > TODAY(), "", IF([% Complete]@row = 1, "Green", IF(OR([Finish Date]@row < TODAY(), [% Complete]@row < .5, AND([% Complete]@row = 0, [Start Date]@row < TODAY())), "Red", IF(OR([Finish Date]@row = TODAY(), [% Complete]@row >= .5), "Yellow"))))
You can also combine the Blank, Green, and Red outputs as above and just tell the formula to generate "Yellow" if all of the previous fail. That would be entirely up to you.
=IF([Start Date]@row > TODAY(), "", IF([% Complete]@row = 1, "Green", IF(OR([Finish Date]@row < TODAY(), [% Complete]@row < .5, AND([% Complete]@row = 0, [Start Date]@row < TODAY())), "Red", "Yellow")))
For testing purposes, you could go with the first but include something along the lines of "Scenario Not Accounted For" if all of them fail including the yellow. This way as you test every possible scenario you can think of, you won't generate a false blank or yellow. Once you figure out that everything is covered, you can switch back to one of the first two or even leave it as is so that if something comes up that you didn't think of in testing, you can be alerted.
=IF([Start Date]@row > TODAY(), "", IF([% Complete]@row = 1, "Green", IF(OR([Finish Date]@row < TODAY(), [% Complete]@row < .5, AND([% Complete]@row = 0, [Start Date]@row < TODAY())), "Red", IF(OR([Finish Date]@row = TODAY(), [% Complete]@row >= .5), "Yellow", "Scenario Not Accounted For"))))
-
These formulas are not working for my smartsheet. Any other ideas/suggestions?
-
Paul Newcome ✭✭✭✭✭✭
What do you mean by "not working"? Are you getting an error? Incorrect results? Unexpected behavior?
-
#UNPARSEABLE is the message I am receiving from all of the formulas you recommended.
-
Paul Newcome ✭✭✭✭✭✭
Have you double checked that the column names are correct?
-
It was user error. I accidentally capitalized one letter which through everything off until I fixed it. This works great! Exactly what I was hoping for.
Thank you
-
Paul Newcome ✭✭✭✭✭✭
That'll do it. Happy to help!️
Please don't forget to mark the most appropriate response(s) as "helpful" so that others looking for a similar solution can know that one may be found here.
Help Article Resources
Categories
Check out theFormula Handbook template!
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109490,"type":"question","name":"HAS exact match within multiselect - Numbered Values","excerpt":"Scenario: Trying to identify a match if a value shows up in a multiselect from another sheet. Approach: I'm able to get 95% of this done through an index(collect(contains))) formula, but having some false positives show up wherever a partial match is found. I later found some suggestions that (has) would be more…","snippet":"Scenario: Trying to identify a match if a value shows up in a multiselect from another sheet. Approach: I'm able to get 95% of this done through an index(collect(contains)))…","categoryID":322,"dateInserted":"2023-08-25T19:26:32+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:49:48+00:00","insertUserID":154049,"insertUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:49:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":154049,"lastUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:49:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":16,"score":null,"hot":3386003780,"url":"https:\/\/community.smartsheet.com\/discussion\/109490\/has-exact-match-within-multiselect-numbered-values","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109490\/has-exact-match-within-multiselect-numbered-values","format":"Rich","lastPost":{"discussionID":109490,"commentID":392694,"name":"Re: HAS exact match within multiselect - Numbered Values","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392694#Comment_392694","dateInserted":"2023-08-26T00:49:48+00:00","insertUserID":154049,"insertUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:49:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/KJPRLKL2FW16\/capture-png.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Capture.PNG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:49:35+00:00","dateAnswered":"2023-08-25T23:58:23+00:00","acceptedAnswers":[{"commentID":392688,"body":"
Hi, <\/p>
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T00:32:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T00:32:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"
