Ratio-Based Conditional Task Health Indicator

Dom Orsler

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.

Ryan Sides

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?

Ryan Sides

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?

Dom Orsler

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?

Dom Orsler

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!

Ryan Sides

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")))

Dom Orsler

Thanks again, Ryan. Awesome.

Ryan Sides

You bet Dom! Happy to help 😀

Dom Orsler

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

Genevieve P.

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

Ryan Sides

@Dom Orsler

Sorry for the delay. I’ve been heads down on a deadline.

@Genevieve P. is spot on as always 😀

Thank you @Genevieve P. !

Genevieve P.

Any time! Hope you made your deadline 🙂

Ryan Sides

@Genevieve P. I did! Was due this morning. Last week was crazy busy so it required a few weekend hours.

Dom Orsler

Most awesome, guys, and many thanks, again. Who knew about the quotes! No wonder I was stuck in an eddy.

Dom Orsler

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.

Andrée Starå

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!

Dom Orsler

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.