Filter Helper Question

Bridget Jones

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

Screen Shot 2021-05-21 at 12.46.57 PM.png

Anupriya

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.

Bridget Jones

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

Trina Brown

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.

Bridget Jones

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.

Genevieve P.

Hi @Bridget Jones

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?

Paul Newcome

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)