SubTasks Due in 7 Days

PMOGal

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.

PMOGal

@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.

Paul Newcome

Are you able to provide a screenshot for context?

Brad Klodowski

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

@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

@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

@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.