Filter Helper Question
Hello,
I am tracking communications for my project, and each row represents a communication (e.g., notify employees of changes to expense policy). I have separate date columns for "Draft Due" and "BU Review Due" and "Legal Review Due" and "Distribution Complete" (see attached). I want to have a filter to show me any rows that have a date in ANY of those columns that is today or in the next 6 days.
I believe I need a column with a formula as a "filter helper", but can't get the formula right.
Can you please help?
Thank you,
Bridget
Answers
-
You can add a checkbox column with a formula - IF(AND(ISBLANK(Column1), ISBLANK(Column2)), 0, 1)
This means when all the columns are blank, the box will be unchecked, otherwise checked.
-
Thanks for the reply, but this does not address my question. I want to filter my sheet for rows where any of the dates in any of the date columns is today or in the next 6 days. Thank you
-
Hi Bridget,
You should be able to do that in the filter. Below is an example. Just make sure you have "at least one condition" selected vs. "all conditions" in the Show rows that match field.
-
This is not helpful, either. This then shows items where Status is already "complete" - it isn't filtering to ONLY show those. I need help with the formula, as described above.
-
Can you clarify all the criteria that you need?
You've listed the 4 date columns and their dates, but it sounds like you also want to exclude any rows where the Status says "Complete".
Are there any other conditions that will indicate a row should be excluded?
-
You want something like this in a checkbox column to check the box on any row that matches your desired criteria.
=IF(AND(Status@row <> "Complete", OR(AND([1st Date Column]@row>=TODAY(), [1st Date Column]@row<=TODAY(6)), AND([2nd Date Column]@row>=TODAY(), [2nd Date Column]@row<=TODAY(6)), AND([3rd Date Column]@row>=TODAY(), [3rd Date Column]@row<=TODAY(6)))), 1)
Help Article Resources
Categories
Check out the Formula Handbook template!
