Tasks Within a Date Range Formula

Brandon Good
Brandon Good ✭✭✭
edited 04/01/20 in Formulas and Functions

Hi,

We meet weekly with a client to show all the tasks that have been worked on since the last meeting (7 days ago). So I am trying to create a Report to show this. Could not get the report builder to filter this exactly as it never captured everything. I need to show the following:


Any task where the Start Date is less than 7 days ago AND the End Date is greater than 1 day ago

OR

Any task where the Start Date is less than 7 days ago AND the End Date is greater than or equal to 7 days ago and less than or equal to 1 day ago

OR

Any task where the Start Date is greater than or equal to 7 days ago and less than and equal to 1 day ago AND the End Date is greater than 1 day ago

OR

Any task where the Start Date is greater than or equal to 7 days ago AND less than or equal to 1 day ago.


=IF(OR(AND([Start Date]1 < TODAY(-7), [End Date]1 > TODAY(-1)), AND([Start Date]1 < TODAY(-7), (AND([End Date]1 >= TODAY(-7), [End Date]1 <= TODAY(-1)))), AND(AND([Start Date]1 >= TODAY(-7), [Start Date]1 <= TODAY(-1)), [End Date]1 > TODAY(-1)), AND([Start Date]1 >= TODAY(-7), [End Date]1 <= TODAY(-1))), β€œYES”, β€œNO”)


This is what I came up with but it shows as #UNPARSEABLE. I cannot seem to get the formula right.

Also if there is a much easier way to capture this data in the report than i may not have thought of please share. I thought it would be a basic filter to show any task that was in progress during a specific date range.

Thanks!

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!