Date In IF Statements

Kate123
Kate123 ✭✭✭✭
edited 01/05/24 in Formulas and Functions

Hello, I have a data set that includes some dates ranges, and based on those dates ranges we want to identify different situations. Examples of these are:

Update #1 AND Update #2 happens on or before X date, then flag as "Situation A"

Update #1 happens on or before X date AND Update #2 happens on or after Y date, then flag as "Situation B"

Update #1 happens between X and Y date AND Update #2 happens during A and B date, then flag as "Situation C"

Update #1 happens between X and Y date AND Update #2 happens on or after A date, then flag as "Situation D"

I want to create a formula that will identify the relevant population at play based on the date range. Can anyone help me with this?

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Kate123

    I used multiple date columns in the formula as I wasn't sure if your data was in multiple columns, or if they were in multiple rows, or if you were going to just hard code the dates in. You can edit the formula and change the date references as needed. The syntax will remain the same.

    =IF(AND([Update #1]@row <= [Start Date1]@row, [Update #2]@row <= [Start Date1]@row), "Situation A", IF(AND([Update #1]@row <= [Start Date1]@row, [Update #2]@row >= [End Date1]@row), "Situation B", IF(AND(AND([Update #1]@row >= [Start Date1]@row, [Update #1]@row <= [End Date1]@row), AND([Update #2]@row >= [Start Date2]@row, [Update #2]@row <= [End Date2]@row)), "Situation C", IF(AND(AND([Update #1]@row >= [Start Date1]@row, [Update #1]@row <= [End Date1]@row), [Update #2]@row >= [Start Date2]@row), "Situation D"))))

    Will this work for you?

    Kelly

  • Kate123
    Kate123 ✭✭✭✭

    Thanks, Kelly. This is very helpful. I keep getting an error message to say Incorrect Argument Set. Not sure what I am doing wrong. I have the dates in multiple columns so was referencing each of those columns in the formula.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!