Two IF functions for one cell
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.
Best Answer
-
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
Answers
-
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
-
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.
-
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?
-
Correct. Sorry for the confusion.
-
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
-
Now it outputs #INVALID OPERATION when inputting the date in Cell B. It should output "CHECK QUOTE VALIDITY".
Everything else is working as expected.
-
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)
-
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
-
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"))
-
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
-
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
-
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?
-
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
-
You're very helpful! Thank you so much!
