IF {Status} is "X" or "XX" and {Date} is in the coming 90 days

Hello,

In the formula below I am asking if the status in a reference sheet is "Pending Submission" or "Pending Approval" then display "No" in a symbol column.

I would be very grateful for any best practice on how to structure the formula to add in if {status} is "Pending Submission" or "Pending Approval" and a {Date Column} is in the next 90 days.

=IF(OR(INDEX(COLLECT({Status}, {country code}, [Country Code]@row), 1) = "Pending Submission", INDEX(COLLECT({Status}, {country code}, [Country Code]@row), 1) = "Pending Approval"), "No", "")

Very thankful in advance,

Protonsponge

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

The Range/Criteria set should go inside of each of the COLLECT functions.

Protonsponge

Thank you very much @Paul Newcome, I got going again with your help - very grateful.

=IF(OR(INDEX(COLLECT({Status}, {country code}, [Country Code]@row, {Date column}, <=TODAY(+90)), 1) = "Pending Submission", INDEX(COLLECT({Status}, {country code}, [Country Code]@row, {Date column}, <=TODAY(+90)), 1) = "Pending Approval"), "No", "")

All comments

Paul Newcome

You would add another range/criteria set to the COLLECT function(s) that need to evaluate for the date.

Protonsponge

Thank you @Paul Newcome for picking up the Q. I was trying to get something like below to work but I am getting #INVALID DATA TYPE.

Is this going down the wrong path?

=IF(AND(OR(INDEX(COLLECT({Status}, {country code}, [Country Code]@row), 1) = "Pending Submission", INDEX(COLLECT({Status}, {country code}, [Country Code]@row), 1) = "Pending Approval"), {Date column}, <=TODAY(+ 90)), "No", "")

Protonsponge

Paul Newcome

The Range/Criteria set should go inside of each of the COLLECT functions.

Protonsponge

Thank you very much @Paul Newcome, I got going again with your help - very grateful.

Paul Newcome

Happy to help. 👍️

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.