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
Best Answer
-
The Range/Criteria set should go inside of each of the COLLECT functions.
Answers
-
You would add another range/criteria set to the COLLECT function(s) that need to evaluate for the date.
-
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
-
The Range/Criteria set should go inside of each of the COLLECT functions.
-
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", "")
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