Date In IF Statements
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
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!