Combining IF/AND/OR to return a value when multiple criteria are met

Sequoia Pinterits

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!

Paul Newcome

Give this a try:

=IF(AND([% Done]@row< 1, OR([Start Date]@row<= TODAY(21), [End Date]@row<= TODAY(21))), 1)

Paul Newcome

Give this a try:

=IF(AND([% Done]@row< 1, OR([Start Date]@row<= TODAY(21), [End Date]@row<= TODAY(21))), 1)

Sequoia Pinterits

@Paul Newcome Works like a charm, thanks!

Paul Newcome

Happy to help. 👍️