SubTasks Due in 7 Days
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
-
@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
-
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!
-
2 ways to do this, depending on how you want to do it:
- COUNTIFS - This is probably the simplest solution, write a COUNTIFS formula which checks the following:
- Finish Date within 7 days
- Range: [Finish Date]:[Finish Date]
- Criteria: AND(@cell < TODAY()+7, @cell > TODAY())
- Status is not complete
- Range: Status:Status
- Criteria: @cell <> "Complete"
- The finished formula might look like (I'm assuming some column names here)
- =COUNTIFS([Finish Date]:[Finish Date], AND(@cell < (TODAY() + 7), @cell > TODAY()), Status:Status, @cell <> "Complete")
- You can also add some additional criteria, e.g. to narrow down to a specific person.
- Finish Date within 7 days
- 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.
- Range to collect - this can be anything that has a value for every row, I usually will use Task Name
- [Task Name]:[Task Name]
- Finish Date within 7 days
- Range: [Finish Date]:[Finish Date]
- Criteria: AND(@cell < TODAY()+7, @cell > TODAY())
- Status is not complete
- Range: Status:Status
- Criteria: @cell <> "Complete"
- The finished formula might look like:
- =COUNT(COLLECT([Task Name]:[Task Name], [Finish Date]:[Finish Date], AND(@cell < (TODAY() + 7), @cell > TODAY()), Status:Status, @cell <> "Complete"))
- Range to collect - this can be anything that has a value for every row, I usually will use Task Name
Either of these should work - happy to help troubleshoot further if they don't.
- COUNTIFS - This is probably the simplest solution, write a COUNTIFS formula which checks the following:
-
@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.
-
@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.
-
@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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!