Ratio-Based Conditional Task Health Indicator

Hi
I'm a bit embarrassed to ask this, as it seems so simple, yet I've been searching high and low for an answer so am resorting to posting a question. I'm relatively new to SS and find the conditional logic interface a bit clunky and hard to use. In summary, I've created a R/Y/G indicator column and am trying to work out how to apply the following logic;
If % complete is greater than or equal to 75% of the percentage of the duration elapsed, green.
If % complete is between 25% and 75% of the percentage of the duration elapsed, yellow.
If % complete is less than or equal to 25% of the percentage of the duration elapsed, yellow.
I'm trying to compare % complete to what proportion of the duration has elapsed and flag accordingly.
Everything I've managed to find so far bases indicators off of today's date in conjunction with due date and % complete, which is not what I need. I'm trying to base indicators off of ratios rather than absolutes.
Thanks.
Best Answer
-
Hi Dom, how are you looking to calculate the duration elapsed? For instance:
Start Date = 01/01/23
Finish Date = 06/01/23
Today's Date = 05/29/23
Days between Start and Finish = 151
Days between today and start = 148
Which is 97% (148/151) of the total available days.
Correct?
If so, then you can use this formula:
=IF([% Complete]@row >= (0.75 * (TODAY() - Start@row) / (Finish@row - Start@row)), "Green", IF([% Complete]@row > (0.24 * (TODAY() - Start@row) / (Finish@row - Start@row)), "Yellow", "Red"))
Is that what you were looking for?
Answers
-
Hi Dom, how are you looking to calculate the duration elapsed? For instance:
Start Date = 01/01/23
Finish Date = 06/01/23
Today's Date = 05/29/23
Days between Start and Finish = 151
Days between today and start = 148
Which is 97% (148/151) of the total available days.
Correct?
If so, then you can use this formula:
=IF([% Complete]@row >= (0.75 * (TODAY() - Start@row) / (Finish@row - Start@row)), "Green", IF([% Complete]@row > (0.24 * (TODAY() - Start@row) / (Finish@row - Start@row)), "Yellow", "Red"))
Is that what you were looking for?
-
Thanks! I think that's getting there. I should have mentioned, obviously, these conditions will be secondary to the first condition, which would make it red if due date has passed and % complete isn't 100, as incomplete tasks past due should always be red.
How and where do you plug this 'code' in? All I know to access is the conditional formatting button, which opens a bit of a clunky rules dialogue that's really constrained by drop downs. Is there another GUI that gives me access to background code?
-
Told you I was a noob! ;-) Ignore my question about 'where do I type the code'. I appreciate how basic that is and have worked it out. I reckon what you've provided is enough to get me there. Thanks again!
-
Here you go. I added the if it hasn't finished yet piece.
=if(and(Finish@row <today(), [% Complete]@row <1), "Red", IF([% Complete]@row >= (0.75 * (TODAY() - Start@row) / (Finish@row - Start@row)), "Green", IF([% Complete]@row > (0.24 * (TODAY() - Start@row) / (Finish@row - Start@row)), "Yellow", "Red")))
-
Thanks again, Ryan. Awesome.
-
Hey Ryan
Asking these questions is a little embarrassing, as I'm sure they're extremely basic. As a final touch to this logic, I'd now like to also turn the indicator red if the end date for the task has passed. Foolishly, I thought this would be a simple nested IF, but I can't get the syntax to parse;
=IF(TODAY() > End@row, “Red”, (IF(Status@row <> "Done", (IF(Duration@row > 0, (IF([% Done]@row >= (0.75 * (TODAY() - Start@row) / (End@row - Start@row)), "Green", IF([% Done]@row > (0.24 * (TODAY() - Start@row) / (End@row - Start@row)), "Yellow", "Red"))))))))
I can't work out why.
Thanks again,
Dom
-
Hey @Dom Orsler
No worries at all - that's what the Community is here for! 🙂
I noticed in your copy/pasted formula that your quotes are a mixture of curved:
“Red”
and straight:
"Done"
Smartsheet needs quotes to be the straight ones in order to parse properly. The best way to get this is to type your formula directly into the sheet (versus copy/pasting from another platform or from notes).
Try your same formula again, but with the proper quotes around "Red":
=IF(TODAY() > End@row, "Red", IF(Status@row <> "Done", IF(Duration@row > 0, IF([% Done]@row >= (0.75 * (TODAY() - Start@row) / (End@row - Start@row)), "Green", IF([% Done]@row > (0.24 * (TODAY() - Start@row) / (End@row - Start@row)), "Yellow", "Red")))))
Cheers,
Genevieve
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
-
Sorry for the delay. I’ve been heads down on a deadline.
@Genevieve P. is spot on as always 😀
Thank you @Genevieve P. !
-
Any time! Hope you made your deadline 🙂
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
-
@Genevieve P. I did! Was due this morning. Last week was crazy busy so it required a few weekend hours.
-
Most awesome, guys, and many thanks, again. Who knew about the quotes! No wonder I was stuck in an eddy.
-
I'm using a symbol type field for this that allows red, yellow or green buttons. I've now been asked to include an orange option. I've looked at the field properties and the options for symbols and can't find a preconfigured one with orange. Is there a way to create a custom symbol field that includes an orange option?
Thanks again.
-
Hi @Dom Orsler
I hope you're well and safe!
You can use other symbols by using, for example, Emojis.
Here's a page you can use.
getemoji.com
Did that work/help?
I hope that helps!
Be safe, and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Sorry - no, that doesn't help. I need coloured buttons, not emoticons. And whether I used emoticons or coloured buttons, I don't know how to create a new button or how to modify the current one.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 432 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 76 Community Job Board
- 504 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!