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?

Find more posts tagged with

Accepted answers

All comments

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

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.