Ratio-Based Conditional Task Health Indicator
Answers
-
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
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
-
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())
-
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
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
-
@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.
-
UNICHAR(11044) is the black dot mentioned above.
Here are some other options as well (all UNICHAR functions):
-
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
-
This is absolutely awesome! Thanks so much to everyone!
-
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!
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.
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 433 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 506 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!