Tasks Within a Date Range Formula

Brandon Good

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!

Paul Newcome

Why don't we try something like this...

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

Paul Newcome

Can you better define your criteria?

For example...

You cannot have a date that is 8 days in the past AND 1 day in the past at the same time as referred to here:

"End Date is greater than or equal to 7 days ago and less than or equal to 1 day ago"

Debbie Sawyer

I haven't tried to re-write your logic - I've just tried to get the brackets etc in the right places!! Will this parse on your sheet?

=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([Start Date]1 >= TODAY(-7), AND([Start Date]1 <= TODAY(-1), [End Date]1 > TODAY(-1))), AND([Start Date]1 >= TODAY(-7), [End Date]1 <= TODAY(-1))), “YES”, “NO”)

Good luck!!

Debbie

Brandon Good

@Paul Newcome I see what you mean with asking it to equate to two different dates. What would be the best way to have it look for a date the is between 7 days in the past and 1 day in the past? I guess if I did greater than TODAY(-8) and less than TODAY(), that would include all those days in my preferred range without needed the "equal to" par of the equation.

@Debbie Sawyer That did not work. And it is likely due to what is mentioned above about the logic of the formula. Thanks though!

I also notice a typo in my 4th criteria. It should read:

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

Brandon

Debbie Sawyer

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

@Brandon Good - sorry it didn't work - I had already assumed the END Date bit in clause 4! I have amended the formula to include Pauls observation but I am sure you have done this already! ;)

Hope it is sorted now for you.

Kind regards

Debbie

Paul Newcome

Why don't we try something like this...

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

Brandon Good

@Paul Newcome YES! Thanks that has worked and it is so simple! I only changed the True and False value but here is the final formula.

=IF(AND([Start Date]1 <= TODAY(-1), [End Date]1 >= TODAY(-7)), "YES", "NO")

Thank you very much! @Debbie Sawyer Thanks as well for your help.

-Brandon

Paul Newcome

Happy to help. 👍️

In a situation like this, you are basically looking for date overlap. I have found that the easiest way for me to look for overlap across two date ranges is to compare Start A to End B and End A to Start B.

I actually had to look at a physical calendar to figure it out.

Because End A should always be after Start A, then overlap will always have End A being greater than or equal to Start B AND Start A will always be less than End B. I hope that makes sense.