Formula Help: Multiple if conditions

EllaSP

I am struggling with the right formula for the following situation.

I have a "Renewal" column that is a date. Not all rows will be relevant, therefore some rows will be blank.

I have a "Asset Condition" column that is set to be one of three symbols, Yes, No, Hold.

I would like the Asset Condition column to return Yes if the date is more than 5 days into the future or if the cell is blank (no date).

I would like the same Asset Condition to return Hold if the date is coming up within the next 5 days.

I would like the same Asset Condition to return No if the date is in the past.

I have managed to get the formula to work as far as the dates, but cannot make it work to read the blank cells.

Screenshot 2025-03-27 100835.png

=IF(Renewal@row < TODAY() + 5, "Hold", IF(Renewal@row < TODAY(), "No", "Yes"))

Can anyone help me with the correct formula please?

Paul Newcome

=IF(OR([Next PAT Test]@row = "N/A", Renewal@row = "N/A"), "Yes", IF(OR([Next PAT Test]@row > TODAY(5), Renewal@row > TODAY(5)), "Yes", IF(OR(……………………

Think of it this way

=IF(logical statement, is true output, is false output)

OR(logical statement 1, logical statement 2, ………….)

The OR becomes the "logical statement" of the IF and will output a "true" if any one of the statements within the OR is true.

Here are a couple of links that I still use that should help you get more comfortable with the various functions.

https://help.smartsheet.com/functions

https://help.smartsheet.com/articles/2476176-formula-error-messages

.

Paul Newcome

Try this:

=IF(Renewal@row = "N/A", "Yes", IF(Renewal@row > TODAY(5), "Yes", IF(Renewal@row < TODAY(), "No", "Hold")))

EllaSP

Hi Paul,

Thank you that worked perfectly!

If I wanted to add another date column "Next PAT Test" where if the date is within the next 5 days = Hold, and if the date is in the past it is No, and if the date is in the future or N/A it is Yes. Would the formula become:

=IF(Next PAT Test@row = "N/A", "Yes", IF(Renewal@row = "N/A", "Yes", IF(Renewal@row > TODAY(5), "Yes", IF(Next PAT Test > TODAY(5), "Yes", IF(Renewal@row < TODAY(), "No", "Hold")))))

I tried this and it says the Syntax is incorrect. I'm trying to learn for the future :)

Screenshot 2025-03-27 115545.png

Paul Newcome

You could do that, but I would use OR statements (helps with organization of the formula). One thing to keep in mind is that column names with spaces, numbers, and/or special characters need to be wrapped in square brackets.

[Nest PAT Test]@row

Another thing to keep in mind, when building a formula, do not build it as a column formula. Change it to a cell formula first and then make your adjustments. That way you can get actual error messages such as #UNPARESABLE which can greatly help with troubleshooting.

EllaSP

Thanks Paul, I really appreciate your help.

I'm not familiar with the OR function. How would this work within the formula I have currently?

Paul Newcome

=IF(OR([Next PAT Test]@row = "N/A", Renewal@row = "N/A"), "Yes", IF(OR([Next PAT Test]@row > TODAY(5), Renewal@row > TODAY(5)), "Yes", IF(OR(……………………

Think of it this way

=IF(logical statement, is true output, is false output)

OR(logical statement 1, logical statement 2, ………….)

The OR becomes the "logical statement" of the IF and will output a "true" if any one of the statements within the OR is true.

Here are a couple of links that I still use that should help you get more comfortable with the various functions.

https://help.smartsheet.com/functions

https://help.smartsheet.com/articles/2476176-formula-error-messages

.

EllaSP

Thank you very much :)

Paul Newcome

Happy to help. 👍️