Calculate task health with status, completion %, and duration

LYK

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!

Kelly Moore

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

Kelly Moore

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

LYK

Thanks @Kelly Moore , it worked!