Calculate task health with status, completion %, and duration

LYK
LYK ✭✭
edited 12/28/23 in Formulas and Functions

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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!