Two IF functions for one cell

SmartsheetRookie

Hi,

Need to do the following:

If Cell B has a date within the next 30 days, Cell A will output CHECK QUOTE VALIDITY. But, if Cell C is checked, leave Cell A blank.

I have the following formula already in Cell A. What should I add to have the above?

=IF(AND([Quote Validity]69 >= TODAY(), [Quote Validity]69 <= TODAY(30)), "CHECK QUOTE VALIDITY", "")

Thanks.

Kelly Moore

Screenshots make it so much easier.

=IF(OR([Quote Validity]@row ="", [No Need to Maintain Quote Validity]@row=1), "", IF(AND([Quote Validity]@row >= TODAY(), [Quote Validity]@row <= TODAY(30)), "CHECK QUOTE VALIDITY"))

Kelly

Kelly Moore

Hey @SmartsheetRookie

Try this

=IF([Column C]@row=1, "", IF(OR(AND([Quote Validity]@row >= TODAY(), [Quote Validity]@row <= TODAY(30)), [Column B]@row-TODAY()<=30), "CHECK QUOTE VALIDITY"))

Unless you need to specifically call out row 69, use the more robust @row designation for your row number. This will allow you to convert your formula to a column formula. Be sure to change the names of columns B and C to reflect your actual column names.

Does this work for you?

Kelly

SmartsheetRookie

https://community.smartsheet.com/discussion/comment/331591#Comment_331591

Close. But now when Cell B is blank, Cell A outputs "CHECK QUOTE Validity". I need Cell A to be blank if Cell B is blank.

Everything else is working properly.

Kelly Moore

Hey

Help me understand the criteria a little better. I understood if Column C was checked then A would be blank. I missed any other criteria that made A blank. So if C is checked or B doesn't have a date, then A is blank? Is that correct?

SmartsheetRookie

https://community.smartsheet.com/discussion/comment/331659#Comment_331659

Correct. Sorry for the confusion.

Kelly Moore

Try this

=IF(OR([Column B]="", [Column C]@row=1), "", IF(OR(AND([Quote Validity]@row >= TODAY(), [Quote Validity]@row <= TODAY(30)), [Column B]@row-TODAY()<=30), "CHECK QUOTE VALIDITY"))

will this work for you?

Kelly

SmartsheetRookie

https://community.smartsheet.com/discussion/comment/331666#Comment_331666

Now it outputs #INVALID OPERATION when inputting the date in Cell B. It should output "CHECK QUOTE VALIDITY".

Everything else is working as expected.

SmartsheetRookie

To rephrase my initial request:

If Cell B has a date within the next 30 days, Cell A will output CHECK QUOTE VALIDITY. But, if Cell C is checked or Cell B is blank, leave Cell A blank. (If Cell B is blank, we do not need to proceed to checking the box in Cell C)

Kelly Moore

Hey

Sorry for the all the back and forth -I'm not sure where there is an error? The first IF is an OR so the two clauses are independent of one another. Either one will produce a blank. If B isn't blank but C has a check, then you will have a blank A. Is your column B and [Quote Validity] the same column? If yes, I can remove the clause after your original formula.

If you could provide a screenshot with some mocked up data showing where you are seeing an unexpected result, it would be helpful.

Kelly

Kelly Moore

My bad. I forgot the @row .

=IF(OR([Column B]@row ="", [Column C]@row=1), "", IF(OR(AND([Quote Validity]@row >= TODAY(), [Quote Validity]@row <= TODAY(30)), [Column B]@row-TODAY()<=30), "CHECK QUOTE VALIDITY"))

SmartsheetRookie

This screenshot: It works properly except it doesnt output "CHECK QUOTE VALIDITY" in that red cell

On this one, the date isn't within 30 days, so the far left cell should be blank.

On this one, it is working properly as it should.

The above formula by @Kelly Moore was used.

EDIT: Here is the one with the relevant headers.

Cell B: Quote Validity, Cell C: No Need to Maintain Quote Validity, Cell A: Validity Ext. Status

Kelly Moore

Screenshots make it so much easier.

=IF(OR([Quote Validity]@row ="", [No Need to Maintain Quote Validity]@row=1), "", IF(AND([Quote Validity]@row >= TODAY(), [Quote Validity]@row <= TODAY(30)), "CHECK QUOTE VALIDITY"))

Kelly

SmartsheetRookie

https://community.smartsheet.com/discussion/comment/331737#Comment_331737

Adding onto this, If I have another checbox column named "Completed", basically if checked, it would output "Completed" instead of "CHECK QUOTE VALIDITY".

How would we modify the formula above?

Kelly Moore

Hey

Because this Completed action trumps all other actions, we would insert the criteria at the beginning of the current IF formula. It is important to remember that a formula advances in execution from left to right and it stops at the first 'True' statement it finds.

=IF(Completed@row=1, "Completed", IF(OR([Quote Validity]@row ="", [No Need to Maintain Quote Validity]@row=1), "", IF(AND([Quote Validity]@row >= TODAY(), [Quote Validity]@row <= TODAY(30)), "CHECK QUOTE VALIDITY")))

Kelly

SmartsheetRookie

https://community.smartsheet.com/discussion/comment/331893#Comment_331893

You're very helpful! Thank you so much!