Changing Value Prefilled Cell Only if Matching Value

Jimrny

The following rows aremovedby 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

@Jimrny<\/a> <\/p>

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.<\/p>","bodyRaw":"[{\"insert\":{\"mention\":{\"name\":\"Jimrny\",\"userID\":138884}}},{\"insert\":\" \\nI 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.\\n\"}]","format":"rich","dateInserted":"2022-05-20T20:24:48+00:00","insertUser":{"userID":145151,"name":"Mike TV","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Mike%20TV","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AGNmyxaedq_6L-MOFFHnCvRUoauO8dJvnzqzFpP7VPvi=s96-c","dateLastActive":"2023-08-07T16:06:52+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/330568#Comment_330568","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/330568#Comment_330568

where would be the best place to insert IFERROR ?

Paul Newcome

@JimrnyYou would wrap the whole thing like so...

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

Jimrny

@Jimrny<\/a> You would wrap the whole thing like so...<\/p>

=IFERROR(original_formula<\/em>, "No Downtime\/Delayed")<\/p>","bodyRaw":"[{\"insert\":{\"mention\":{\"name\":\"Jimrny\",\"userID\":138884}}},{\"insert\":\" You would wrap the whole thing like so...\\n\\n=IFERROR(\"},{\"attributes\":{\"italic\":true},\"insert\":\"original_formula\"},{\"insert\":\", \\\"No Downtime\\\/Delayed\\\")\\n\"}]","format":"rich","dateInserted":"2022-05-23T13:06:19+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-25T17:46:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/330657#Comment_330657","embedType":"quote"}"> 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

我需要减少额外的最后“分钟”, 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

That last bit with the "Mins" should actually come before the output of the IFERROR like so:<\/p>

=IFERROR(<\/strong>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")<\/strong><\/p>","bodyRaw":"[{\"insert\":\"That last bit with the \\\"Mins\\\" should actually come before the output of the IFERROR like so:\\n=\"},{\"attributes\":{\"bold\":true},\"insert\":\"IFERROR(\"},{\"insert\":\"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\\\"//www.santa-greenland.com/community/discussion/91351/\"},{\"attributes\":{\"bold\":true},\"insert\":\", \\\"No Downtime\\\/Delayed\\\")\"},{\"insert\":\"\\n\"}]","format":"rich","dateInserted":"2022-05-23T14:54:51+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-25T17:46:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/330669#Comment_330669","embedType":"quote"}"> 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

You forgot to include that first IF statement.<\/p>

=IFERROR(IF(<\/strong>Difference@row..................<\/p>","bodyRaw":"[{\"insert\":\"You forgot to include that first IF statement.\\n\\n=IFERROR(\"},{\"attributes\":{\"bold\":true},\"insert\":\"IF(\"},{\"insert\":\"Difference@row..................\\n\"}]","format":"rich","dateInserted":"2022-05-23T17:27:19+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-25T17:46:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/330707#Comment_330707","embedType":"quote"}"> 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

My apologies. Missed a closing parenthesis.<\/p>

=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")<\/p>","bodyRaw":"[{\"insert\":\"My apologies. Missed a closing parenthesis.\\n\\n=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\\\")\\n\"}]","format":"rich","dateInserted":"2022-05-23T20:04:56+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-25T17:46:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/330740#Comment_330740","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/330740#Comment_330740

Thank you!! It's working properly now