Changing Value Prefilled Cell Only if Matching Value

Jimrny

The following rows are moved by automation to this sheet with without the formulas just to display the values. What is the best way to have any cell under "Duration" column that if it matches "#INVALID VALUE" to Display "No Downtime/Delayed" without clearing the rest of the column?

Paul Newcome

My apologies. Missed a closing parenthesis.

=IFERROR(IF(Difference@row < 0, ROUNDDOWN(Difference@row / 60, 0) + " hrs " + (60 - MOD(Difference@row, 60)), ROUNDDOWN(Difference@row / 60, 0) + " hrs " + MOD(Difference@row, 60) + " mins"), "No Downtime/Delayed")

Mike TV

@Jimrny

I would add an IFERROR function to the sheet it's coming over from that will display that text if it's getting an error. If that's not possible, then you'll need to add a helper column which has =IF(CONTAINS( and have it look for the value of "#INVALID VALUE" and replace it with what you'd like it to display. Then hide the duration column that's copied over from the original sheet.

Jimrny

https://community.smartsheet.com/discussion/comment/330568#Comment_330568

where would be the best place to insert IFERROR ?

Paul Newcome

@Jimrny You would wrap the whole thing like so...

=IFERROR(original_formula, "No Downtime/Delayed")

Jimrny

https://community.smartsheet.com/discussion/comment/330657#Comment_330657

I wrapped it like this:

=IFERROR(Difference@row < 0, ROUNDDOWN(Difference@row / 60, 0) + " hrs " + (60 - MOD(Difference@row, 60)), ROUNDDOWN(Difference@row / 60, 0) + " hrs " + MOD(Difference@row, 60), "No Downtime/Delayed") + " mins"

And now I'm getting #INCORRECT ARGUMENT SET

Do I need to drop the additonal " mins" at the end, I added that to give me hrs/mins for duration. I'm guessing it would of given me "No Downtime/Delayed mins" if there was an error?

I'm trying to get duration to display XX hrs XX mins and if there's an error, to display "No Downtime/Delayed"

Paul Newcome

That last bit with the "Mins" should actually come before the output of the IFERROR like so:

=IFERROR(Difference@row < 0, ROUNDDOWN(Difference@row / 60, 0) + " hrs " + (60 - MOD(Difference@row, 60)), ROUNDDOWN(Difference@row / 60, 0) + " hrs " + MOD(Difference@row, 60) + " mins", "No Downtime/Delayed")

Jimrny

https://community.smartsheet.com/discussion/comment/330669#Comment_330669

I made the adjustment but it keeps giving me "Incorrect Argument Set", hmm.

Paul Newcome

You forgot to include that first IF statement.

=IFERROR(IF(Difference@row..................

Jimrny

https://community.smartsheet.com/discussion/comment/330707#Comment_330707

It's still giving me an "INCORRECT ARGUMENT SET" with the following:

=IFERROR(IF(Difference@row < 0, ROUNDDOWN(Difference@row / 60, 0) + " hrs " + (60 - MOD(Difference@row, 60)), ROUNDDOWN(Difference@row / 60, 0) + " hrs " + MOD(Difference@row, 60) + " mins", "Delayed, No Downtime"))

Am I missing an additional argument?

Paul Newcome

My apologies. Missed a closing parenthesis.

=IFERROR(IF(Difference@row < 0, ROUNDDOWN(Difference@row / 60, 0) + " hrs " + (60 - MOD(Difference@row, 60)), ROUNDDOWN(Difference@row / 60, 0) + " hrs " + MOD(Difference@row, 60) + " mins"), "No Downtime/Delayed")

Jimrny

https://community.smartsheet.com/discussion/comment/330740#Comment_330740

Thank you!! It's working properly now 😄