Schedule Health based on % Complete gernerates errors in some rows.

Jemari

I need help troubleshooting a formula (pasted below). In some rows it works fine, and in others I get an error, "#INVALID OPERATION". I expect the error ina row where NETWORKDAYS and DURATION cannot be calculated because the startdate and end date are not populated. Does anyone know what I am missing?

Thanks in advance!

=IF([% Complete]@row >= NETWORKDAYS([Start Date]@row, TODAY()) / Duration@row, "Green", IF(([% Complete]@row >= NETWORKDAYS([Start Date]@row, TODAY()) / Duration@row) - 0.05, "Yellow", "Red"))

Paul Newcome

Give this a try:

=IFERROR(IF([% Complete]@row >= MIN(1, NETWORKDAYS([Start Date]@row, TODAY()) / Duration@row), "Green", IF([% Complete]@row >= (NETWORKDAYS([Start Date]@row, TODAY()) / Duration@row) - 0.05, "Yellow", "Red")), "")

Paul Newcome

Try an IFERROR.

=IFERROR(IF([% Complete]@row >= NETWORKDAYS([Start Date]@row, TODAY()) / Duration@row, "Green", IF(([% Complete]@row >= NETWORKDAYS([Start Date]@row, TODAY()) / Duration@row) - 0.05, "Yellow", "Red")), "")

Jemari

Just getting time to get back to this. Thank you @Paul Newcome! Your improvement helped remove the error! 🥇There are still some open issues I am tinkering around with. Unfortunately just starting to learn formulas in Smartsheet so I am slow as he!!.

I created some "tinkering" columns to prove that the "Days used" and "expected % complete" parts of your formula work correctly. All good there! When I put it all together there are a couple cracks.

1) It is not working for rows that are 100% complete. I think this is because the "expected % complete" part of the formula:

=NETWORKDAYS([Start Date]@row, TODAY()) / Duration@row

...calculates to a value greater than 1 when the end date is in the past, but the % complete is never greater than 100.

2) Yellow and red rows are not working still. I don't have a theory for that yet. I seem to be tripping on the 0.05 calc somehow. The formula certainly LOOKS good... I keep tinkering with spaces, brackets and parens in the formula. So far to no avail. There has to be something about how smartsheet parses logic that I just don't know yet.

On the positive side, the logic in your formula elegantly returns a green symbol for rows that have not started yet. Woot! 🍾

Best,

J

Jemari

@Genevieve P. - I saw you were an absolute wizard with another schedule health formula someone used, but I am trying to create one that is agnostic to the manually entered status. If you are able to give advice on the following formula, it is welcome! There is an image of the columns used earlier in this thread.

I am currently using the formula

=IFERROR(IF([% Complete]@row >= NETWORKDAYS([Start Date]@row, TODAY()) / Duration@row, "Green", IF(([% Complete]@row >= NETWORKDAYS([Start Date]@row, TODAY()) / Duration@row) - 0.05, "Yellow", "Red")), "")

but I am not returning a color symbol for rows that should be yellow or red, and I am not returning a green symbol for rows where the row is 100% complete (because the end date occurs in the past). I think I need an an OR or AND operator, but I am not following the logic of how Smartsheet uses these yet.

Best,

J

Paul Newcome

Give this a try:

=IFERROR(IF([% Complete]@row >= MIN(1, NETWORKDAYS([Start Date]@row, TODAY()) / Duration@row), "Green", IF([% Complete]@row >= (NETWORKDAYS([Start Date]@row, TODAY()) / Duration@row) - 0.05, "Yellow", "Red")), "")

Genevieve P.

Hi @Jemari

Apologies for the delay! I see that @Paul Newcome answered your question as another formula wizard 🙂 🧙‍♂️