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!

Best Answer

Answers

  • Paul Newcome
    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
    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

  • @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
    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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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

  • Paul Newcome
    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!