SubTasks Due in 7 Days

PMOGal
PMOGal ✭✭✭✭✭
edited 05/16/24 in Formulas and Functions

Need help with a formula to determine how many subtasks are due in 7 days that are not complete.

Columns are Finish (which is a date), Status <> Complete. I also have a Helper Parent Column that has no data (blank) for the main Tasks. Had a formula that used to work but no longer is producing the correct result.

Best Answer

  • PMOGal
    PMOGal ✭✭✭✭✭
    Answer ✓

    @Brad Klodowski I got it to work, with one more piece of criteria. In my original formula I had to add Status<>Complete

    I went from 12 to 10 (the correct number) with this formula:

    =COUNTIFS(Finish:Finish, <=TODAY(+7), Finish:Finish, >=TODAY(), [Helper Parent Column]:[Helper Parent Column], <>"", Status:Status, <>"Complete")

    Still like your Count Formula. Thanks for your help.

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you able to provide a screenshot for context?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • 2 ways to do this, depending on how you want to do it:

    1. COUNTIFS - This is probably the simplest solution, write a COUNTIFS formula which checks the following:
      1. Finish Date within 7 days
        1. Range: [Finish Date]:[Finish Date]
        2. Criteria: AND(@cell < TODAY()+7, @cell > TODAY())
      2. Status is not complete
        1. Range: Status:Status
        2. Criteria: @cell <> "Complete"
      3. The finished formula might look like (I'm assuming some column names here)
        1. =COUNTIFS([Finish Date]:[Finish Date], AND(@cell < (TODAY() + 7), @cell > TODAY()), Status:Status, @cell <> "Complete")
        2. You can also add some additional criteria, e.g. to narrow down to a specific person.
    2. COUNT(COLLECT) - This is a bit more complicated, will accomplish the same thing, and can be way more powerful in the future / other scenarios. COLLECT has some different syntax, where you choose what you're collecting first, then specify ranges and criteria similar to COUNTIFS.
      1. Range to collect - this can be anything that has a value for every row, I usually will use Task Name
        1. [Task Name]:[Task Name]
      2. Finish Date within 7 days
        1. Range: [Finish Date]:[Finish Date]
        2. Criteria: AND(@cell < TODAY()+7, @cell > TODAY())
      3. Status is not complete
        1. Range: Status:Status
        2. Criteria: @cell <> "Complete"
      4. The finished formula might look like:
        1. =COUNT(COLLECT([Task Name]:[Task Name], [Finish Date]:[Finish Date], AND(@cell < (TODAY() + 7), @cell > TODAY()), Status:Status, @cell <> "Complete"))

    Either of these should work - happy to help troubleshoot further if they don't.

  • PMOGal
    PMOGal ✭✭✭✭✭

    @Brad Klodowski Both tips above seem logical. They do not work for me. Today, I have 10 subtasks due. The formulas supplied above are returning a 0 value.

  • Brad Klodowski
    Brad Klodowski ✭✭✭
    edited 05/17/24

    @PMOGal any chance you can provide a screenshot of the data you're working with? I've tested both formulas as I wrote them in a testing sheet I have and both seem to work, so it might be something we need to work out with your data set.

  • PMOGal
    PMOGal ✭✭✭✭✭
    Answer ✓

    @Brad Klodowski I got it to work, with one more piece of criteria. In my original formula I had to add Status<>Complete

    I went from 12 to 10 (the correct number) with this formula:

    =COUNTIFS(Finish:Finish, <=TODAY(+7), Finish:Finish, >=TODAY(), [Helper Parent Column]:[Helper Parent Column], <>"", Status:Status, <>"Complete")

    Still like your Count Formula. Thanks for your help.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!