Ratio-Based Conditional Task Health Indicator

Genevieve P.

Hi @Dom Orsler

There currently isn't a symbol column that has an orange button specifically. You could either select the dropdown that includes Grey or Blue as alternate options.

Otherwise, you could create your own dropdown column and use your own custom symbols (e.g. emojis) as @Andrée Starå suggested!

Feel free to add your vote and voice to this Product Idea as well: Symbol Flexibility - more symbol colors

Cheers,

Genevieve

breso

Hi @Dom Orsler, this is a variation of the formula I am using that compares % complete to days elapsed. It first checks if the task is a milestone (Start = Finish) so I don't divide by zero. Then it populates a "Symbol" column with red, yellow, green, or blue circles. It also marks any tasks not ending in the next 30 workdays as blank ("").

The only thing that is confusing me is if I should add 1 or subtract 1 when using the networkdays function. @Genevieve P. any ideas what is the correct way to do that with this function? Thanks!

=IF([Start Date]@row <> [End Date]@row, IF([% Complete]@row = 1, "Blue", IF(AND([End Date]@row < TODAY(), [% Complete]@row <> 1), "Red", IF(AND([Start Date]@row <= TODAY(), ABS(NETWORKDAYS(TODAY(), [Start Date]@row) + 1) / ABS(NETWORKDAYS([Start Date]@row, [End Date]@row)) > [% Complete]@row), "Yellow", IF(AND([Start Date]@row <= TODAY(), ABS(NETWORKDAYS(TODAY(), [Start Date]@row) + 1) / ABS(NETWORKDAYS([Start Date]@row, [End Date]@row)) <= [% Complete]@row), "Green", IF(AND([Start Date]@row > TODAY(), [End Date]@row <= WORKDAY(TODAY(), 30)), "Green", ""))))), PARENT())

Genevieve P.

Hey @breso

This depends on how you want to count your days. In your case, I believe you'd want to -1

This is because the NETWORKDAYS between Today and Today = 1

Today and Tomorrow = 2

So if you want Wed 21 - Thurs 22 to be 1d duration, then you'd want to remove 1. Does that make sense?

Cheers,

Genevieve

Paul Newcome

@Dom Orsler I will have to do some digging for the details, but there is a way to get many more colors within the colored status balls.

Basically you would use a similar nested IF, but instead of outputting a specific color ("Red", "Yellow", "Green", etc.) you would output a basic text indicator ("R", "Y", "G", "Or", "Blu", etc.) into a hidden helper column.

There is a specific UNICHAR function that will output a black ball but it is treated as a text type character so you can use conditional formatting to change the color of the ball based on the output in your helper column. The specific UNICHAR function is what I need to dig for.

In theory, you can use this to get colored balls for every color allowed in Conditional formatting which is 40 if you include White.

Paul Newcome

@Dom Orsler

UNICHAR(11044) is the black dot mentioned above.

Here are some other options as well (all UNICHAR functions):

breso

Thank you, @Genevieve P.!

That makes sense and the formula works much better with the -1. I tried it out below and verified the % elapsed by using the % complete. I had the +1 from the Advanced Formula examples (below) where it seemed situational like if before the start date then I would use +1 instead. Sorry if this thread isn't the correct forum for digging into this.

Net Work Days Elapsed = NETWORKDAYS([Start Date]@row, Today@row) - 1 (or +0 or +1)

% Elapsed = [Net Work Days Elapsed]@row / [Net Work Days Total]@row

Dom Orsler

This is absolutely awesome! Thanks so much to everyone!

Andrée Starå

@Dom Orsler

Happy to help!

✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!

Paul Newcome

Happy to help. 👍️