checkbox if statement based on date and multiple drop down options

Roy Turner

I'm trying to set up a formula that will check an "At Risk" flag based on a date and several drop down options. Example for row 7 : If the [Ship Date Estimated]7 is less than Today and the [Status]7 does not equal "Shipped" then check the flag or if the [Delivery Date Estimate] is less than today and the Status does not equal "Received" or "Online Order" or "Delivered" then check the flag. 

But I haven't found a way to combine And statements and Or statements into the same formula but couldn't figure out how to do it with the online references I found. Any help is appreciated.

Thanks for any help.

Paul Newcome

The first step is listing out all sets of criteria you would want to trigger a flag. From there you would want to put them in order of priority. After that it will be much easier to build the formula.

L_123

If the [Ship Date Estimated]7 is less than Today and the [Status]7 does not equal "Shipped" then check the flag

=if(and([Ship Date Estimated]@row < today(),Status7 <>"Shipped",1)

or if the [Delivery Date Estimate] is less than today and the Status does not equal "Received" or "Online Order" or "Delivered" then check the flag. 

if([Delivery Date Estimate]@row < today(), if(and(Status@row <> "Received",Status@row<>"Online Order", Status@row <> "Delivered"),1

 

Then put them together

=if(and([Ship Date Estimated]@row < today(),Status7 <>"Shipped"),1,if([Delivery Date Estimate]@row < today(), if(and(Status@row <> "Received",Status@row<>"Online Order", Status@row <> "Delivered"),1

 

Potentially a typo, didn't test.

Eric M Oliveira

Hello,

 

Happy to help! 

 

L@123 is correct in that when utilizing formulas it's best to break out the formulas to achieve your desired goal then piece them together. As stated you'll want to create a Nested IF Formula to achieve this desired result. 

 

1. For the first formula, "If the [Ship Date Estimated]7 is less than Today and the [Status]7 does not equal "Shipped" then check the flag", you'll want to utilize a combination of an IF(AND( function with a NOT Function to equal not "Shipped" in the formula. This could look like this: 

 

=IF(AND([Ship Date Estimated]@row < TODAY(), NOT([Status]@row = "Shipped")), 1)

 

2. For the second part, "If the [Delivery Date Estimate] is less than today and the Status does not equal "Received" or "Online Order" or "Delivered" then check the flag.", you'll want to utilize a combination of an IF(AND( function with a NOT(OR( Function to equal not "Received" or "Online Order" or "Delivered" in the formula. You would not want to utilize an NOT(AND because this would only check the flag if all values applied. This could look like this: 

 

=IF(AND([Delivery Date Estimate]@row < TODAY(), NOT(OR([Status]@row = "Received", [Status]@row = "Online Order", [Status]@row = "Delivered"))), 1)

 

As stated you'll want to combine the two formula to utilize them within the same cell. Which would look like this:

 

=IF(AND([Ship Date Estimated]@row < TODAY(), NOT([Status]@row = "Shipped")), 1, IF(AND([Delivery Date Estimate]@row < TODAY(), NOT(OR([Status]@row = "Received", [Status]@row = "Online Order", [Status]@row = "Delivered"))), 1, 0))

 

Have a wonderful day,

Eric  -  Smartsheet Technical Support