Tasks Within a Date Range Formula
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!
Best Answer
-
Why don't we try something like this...
=IF(AND([Start Date]@row <= TODAY(-1), [End Date]@row >= TODAY(-7)), 1)
Answers
-
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"
-
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
-
@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
-
=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
-
Why don't we try something like this...
=IF(AND([Start Date]@row <= TODAY(-1), [End Date]@row >= TODAY(-7)), 1)
-
@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
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!