checkbox if statement based on date and multiple drop down options

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.

Find more posts tagged with

Formulas

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Contact List showing as a text only
It happened suddenly. It was working last week. I'm using Index Collect to pull a contact as cross sheet reference. But today, I did not see that working well. Usually it has circle beside my name, suddenly it vanishes, and it just contains text. I'm using those other columns to be sent notification, and it doesn't work…
Formula to Convert Scientific Notation to Numerical Value
Hi, I am trying to create a sheet from data that is originally held in .csv files. My goal is to make it so that transferring the data to my sheet should be as simple as just copy and paste. My issue is that two of the fields that I need to copy over are reported in scientific notation (1.3E+004) and I am trying to figure…
Calculating Quarters with a Start and a Finish date
I searched the community and did not see a formula that calculated Quarter based on a Start and a Finish date while also accounting for cases where the year for the Start and Finish date differ. The formula below handles that. A few notes … It assumes that Finish is always >= Start. The entire formula is wrapped with…