Conditional Formatting Rules not working

alowe

I have a formula in my sheet that calculates Actual Duration. (This is in addition to my Planned Duration column, for which I use Smartsheet's default duration capability to calculate). I am trying to enable a conditional formatting in my sheet that turns a cell white (text not visible) when there is an #INVALID DATA TYPE. I know the formula works correctly because when both dates needed to calculate the number are filled in, it correctly populates. I've also tried the reverse of this to tell it to turn white if Actual Duration is not a number, and that doesn't work either.

Jennifer Kurtz

I don't think there are any silly questions. :)

If all you're looking to do is remove the formula errors in that column, you can use: =IFERROR(NETWORKDAYS([Actual Start Date]@row, [Actual End Date]@row), ""

And that will remove the error message! Essentially, the IFERROR formula is returning a blank if the formula results in an error.

If you had plans for other kinds of conditional formatting based on that column, though, you could put any text of your choice between the parentheses (like, "Error"). Your conditional formatting rule could simply look for the text you specify to apply the formatting.

I hope that helps!

Jennifer Kurtz

Seems like the conditional formatting rule doesn't like the actual error message in Smartsheet. A workaround would be to wrap your formula in the "IFERROR" function, and include whatever text you'd like (e.g., "Error") - and then use that text for the conditional formatting rule.

alowe

Thanks @Jennifer Kurtz ! Silly question, if this is my formula:
=NETWORKDAYS([Actual Start Date]@row, [Actual End Date]@row)

What would that look like?

Jennifer Kurtz

I don't think there are any silly questions. :)

If all you're looking to do is remove the formula errors in that column, you can use: =IFERROR(NETWORKDAYS([Actual Start Date]@row, [Actual End Date]@row), ""

And that will remove the error message! Essentially, the IFERROR formula is returning a blank if the formula results in an error.

If you had plans for other kinds of conditional formatting based on that column, though, you could put any text of your choice between the parentheses (like, "Error"). Your conditional formatting rule could simply look for the text you specify to apply the formatting.

I hope that helps!

alowe

@Jennifer Kurtz That worked BEAUTIFULLY! I cannot thank you enough!!!

Jennifer Kurtz

Oh great! So glad that was helpful. :) Have a great day!!