Filtering Tasks by Date

Alan Praysman

Hello Community,

i'm trying to create a Filter - Current Tasks, on the Project Schedule sheet. The filter would have Start Date less than or equal to Calendar Date and End Date greater than or equal to Calendar Date. The options available are:

1. "In the past", "In the future" - these are not including Calendar Date.
2. Less/Greater or equal to - these require specific date, and don't accept TODAY() entry.

Is there a way to create Filter using formulas?

Thanks anyone in advance.

ap

Andrée Starå

Hi Alan,

Yes, it’s possible to use so-called helper columns with formula if needed.

Try something like this.

Add a checkbox helper column and use the formula below and then filter on this column and that the checkbox is checked.

=IF(AND([Start Date]@row <= TODAY(); [End Date]@row >= TODAY()); 1)

The same version but with the below changes for convenience.

=IF(AND([Start Date]@row <= TODAY(), [End Date]@row >= TODAY()), 1)

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma."

Did that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer! 

Andrée Starå

Hi Alan,

Yes, it’s possible to use so-called helper columns with formula if needed.

Try something like this.

Add a checkbox helper column and use the formula below and then filter on this column and that the checkbox is checked.

=IF(AND([Start Date]@row <= TODAY(); [End Date]@row >= TODAY()); 1)

The same version but with the below changes for convenience.

=IF(AND([Start Date]@row <= TODAY(), [End Date]@row >= TODAY()), 1)

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma."

Did that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer! 

Alan Praysman

Hi Andree,

thanks for your help again. This helper column "trick" is a great tip and does exactly what I need now. I already see how i'll use it for other things.

I appreciate you help.

-Alan

Andrée Starå

Excellent!

Happy to help!