IF statement with multiple conditions and columns

Jones22

Hi,

I need to create a formula that:

If a machine work order is complete, or canceled the departure date is 27 days after Complete Work Order date.

If a machine work order says "#NO MATCH", then departure date is 27 days after arrival date.

The formula I am using is as follows:

=IF(OR([Work Order Status]@row = "Complete", [Work Order Status]@row = "Canceled"), [Work Order Complete Date]@row + 27, IF([Work Order Status]@row = "#NO MATCH", Arrival@row + 27))

It only works for machines that have completed or canceled work orders. Any help is appreciated.

KPH

Hi @Jones22

It looks good to me.

=IF(OR([Work Order Status]@row = "Complete", [Work Order Status]@row = "Canceled"), [Work Order Complete Date]@row + 27, IF([Work Order Status]@row = "#NO MATCH", Arrival@row + 27))

When you say it doesn't work, what does it do?

If it is blank it could be that the text in Work Order Status isn't exactly #NO MATCH

Or rather than "If a machine work order says "#NO MATCH"" are you looking for if the Work Order Status is anything other than Complete or Canceled. If so, then you don't need the second IF, you can just put the calculation as the value if false from the first the formula. Like this:

=IF(OR([Work Order Status]@row = "Complete", [Work Order Status]@row = "Canceled"), [Work Order Complete Date]@row + 27, Arrival@row + 27)

Lauren Dominique

Two thoughts:

First, to answer your problem:

1) Get rid of the second IF and use the formula =IF(OR([Work Order Status]@row = "Complete", [Work Order Status]@row = "Canceled"), [Work Order Complete Date]@row + 27, Arrival@row + 27)

By writing it this way your IF statement is basically saying: IF work status is “Complete” OR “Cancelled”, add 27 days to complete date; and if work status is neither “Complete” or “Cancelled”, add 27 days to arrival date.

Second, to give you a tip:

2) You can solve for “NO MATCH” to have an actual status in that field instead of an error. You’d just need to add the IFERROR function to the beginning of the formula you have that is returning NO MATCH in some cells.

If you haven’t used the IFERROR function before it’s quite easy- you’ll just add =IFERROR( to the beginning of your existing formula, and then add a comma to the very end. Then after the comma, you’ll tell Smartsheet what you want it to do if there is a NO MATCH error. So, it could look, for example, like this:

=IFERROR(existing formula, “Delayed”)

In that case, instead of NO MATCH, those cells would return “Delayed” as the status instead. So, you’d just swap out “Delayed” with whatever text you wanted to show instead of NO MATCH. You can also choose to leave it blank, in which case you’d just put “ “).

#2 will not have an effect on what you’re trying to accomplish in #1, but thought I’d share anyways for the sake of cleaning up your “Work Order Status” column with true status names.

Hope this helps!

Jones22

Thank you for the help! I used the IFERROR method and everything looks and works cleaner.