IF statement - Less than 3 weeks from today

Anthony Nicholas

Hello,

I am looking for help on a formula for IF([Proposed Effective Date]@row is less than 3 weeks from today display "Rejected" AND IF([Have you received quotes form competitors?] = Yes then display "Rejected"

So if one condition applies it displays "Rejected" or if both then "Rejected" displays as well.

Thank you so much!

DKazatsky2

Hi @Anthony Nicholas,

Give this a try:

=IF(OR([Proposed Effective Date]@row < TODAY(21), [Have you received quotes from the competitors?]@row = "Yes"), "Rejected")

If you are rejecting based on either conditioner, you don't need to check if both exist at the same time (AND) you only care if either one matches (OR).

If I am misunderstanding your criteria, please let me know.

Hope this helps,

Dave

Anthony Nicholas

https://community.smartsheet.com/discussion/comment/393454#Comment_393454

Thank you so much.

Is there a way to also include IF([Other Lines of Coverage Needed]@row = "Marine", "Farm", "Aviation", "Agriculture" then display "Rejected" I tried to add that in but the formula kept coming back as invalid.

DKazatsky2

@Anthony Nicholas

Do you want to reject based on one of the new conditions existing on their own or existing along with one of the original conditions?

Anthony Nicholas

@DKazatsky2

Reject based on any of the conditions. So if any or all of the above conditions apply then displays "Rejected"

So IF(OR([Proposed Effective Date]@row < TODAY(21), [Have you received quotes from the competitors?]@row = "Yes"), AND IF([Other Lines of Coverage Needed]@row = "Marine", "Farm", "Aviation", "Agriculture" then display "Rejected"

DKazatsky2

@Anthony Nicholas ,

You can add them inside the existing OR section...

=IF(OR([Received Date]@row > TODAY(21), [Have you received quotes from the competitors?]@row = "Yes", [Other Lines of Coverage Needed]@row = "Marine", [Other Lines of Coverage Needed]@row = "Farm", [Other Lines of Coverage Needed]@row = "Aviation", [Other Lines of Coverage Needed]@row = "Agriculture"), "Rejected")

Dave