Calculate task health with status, completion %, and duration
Hi wondering if anyone could help shed some light on this formula. I'm trying to calculate task health taking into consideration of the status, completion %, and duration. Found a formula in one of the discussion that suits, however, when the duration is 0 as a milestone, it came back with divide by zero error. I tried to tweak the formula to just show green health if it comes across duration as 0 but still getting the divide by zero error.
=IF(OR(Status@row = "On Hold", Status@row = "Blocked"), "Gray", IF(AND(TODAY() > [End Date]@row, [%Complete]@row <> 1), "Red", IF(AND([%Complete]@row < 0.5, Duration@row > 0, NETWORKDAYS([Start Date]@row, TODAY()) / Duration@row > 0.5), "Yellow", IF(Duration@row = "0", "Green"))))
Would be grateful if anyone can help see what went wrong/missing. Thanks in advance!
Best Answer
-
Hello @LYK
Try this. I added an IFERROR so your YELLOW statement. I also flipped the YELLOW GREATER THAN 0.5 (NETWORKDAYS([Start Date]@row, TODAY()) / Duration@row > 0.5) to a LESS THAN. It seems to me the calculated value would be small decimals.
=IF(OR(Status@row = "On Hold", Status@row = "Blocked"), "Gray", IF(AND(TODAY() > [End Date]@row, [%Complete]@row <> 1), "Red", IF(AND([%Complete]@row < 0.5, Duration@row > 0, IFERROR(NETWORKDAYS([Start Date]@row, TODAY()) / Duration@row, 0) < 0.5), "Yellow", IF(Duration@row = 0, "Green"))))
Will this work for you?
Kelly
Answers
-
Hello @LYK
Try this. I added an IFERROR so your YELLOW statement. I also flipped the YELLOW GREATER THAN 0.5 (NETWORKDAYS([Start Date]@row, TODAY()) / Duration@row > 0.5) to a LESS THAN. It seems to me the calculated value would be small decimals.
=IF(OR(Status@row = "On Hold", Status@row = "Blocked"), "Gray", IF(AND(TODAY() > [End Date]@row, [%Complete]@row <> 1), "Red", IF(AND([%Complete]@row < 0.5, Duration@row > 0, IFERROR(NETWORKDAYS([Start Date]@row, TODAY()) / Duration@row, 0) < 0.5), "Yellow", IF(Duration@row = 0, "Green"))))
Will this work for you?
Kelly
-
Thanks @Kelly Moore , it worked!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!