Combining IF/AND/OR to return a value when multiple criteria are met
Hi Everyone,
I am trying to filter out tasks on my Gantt chart occurring over the next three weeks. We found a good combination of filters that works for reports, however I want to be able to create a filter like this for the sheet so that the tasks can easily be edited in real time. Sheets do not allow for the hierarchies of filtering, and I'm not able to make edits on reports (I think because use dependencies).
As a workaround, I'm hoping there is a way to create a helper column that returns a "1" if a task meets my conditions. Below are the filters we use on the report (the Shop filter is not necessary for this helper column). Basically, I want to see:
- Any tasks with start dates in the past
- Any tasks with start dates in the next 21 days
- Any tasks with end dates in the next 21 days
- ALL tasks above must have a [% Done] value of less than 1
This is where I got on my own, but it just returns a "1" for any column that has a [% Done] value of less than one. IF(AND([% Done]@row < 1), 1) OR([End Date]@row <= TODAY(21)), 1, IF([Start Date]@row <= TODAY(21), 1))
Thanks!
Best Answer
-
Give this a try:
=IF(AND([% Done]@row< 1, OR([Start Date]@row<= TODAY(21), [End Date]@row<= TODAY(21))), 1)
Answers
-
Give this a try:
=IF(AND([% Done]@row< 1, OR([Start Date]@row<= TODAY(21), [End Date]@row<= TODAY(21))), 1)
-
@Paul Newcome Works like a charm, thanks!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!