Formula for counting tasks due in the next 7 days
Hello, I've created the below formula to count upcoming tasks that are due within the next 7 days. I prefer a single formula to also include "In Progress", "Not Started", or "On Hold" status and just planned on creating more countifs and summing them together. However, just testing the formula for Not Started, which I know there are 3 due in the next 7 days, it's retuning #UNPARSEABLE. When I remove the Due Date portion of the formula, I get the the total for every task in the project plan that's not started, so it has something to do with the way I have the Due Date.
=COUNTIFS([Row Type]:[Row Type], "Task", Status:Status, "Not Started", [Due Date]:[Due Date], AND(@cell <= TODAY(+7), @cell >= TODAY()))
Open to if maybe there is a better way to do this. I created a report and it pulls in 19 tasks due within the next 7 days (3 not started, 16 in progress). I just want the total count though as it's being displayed on a dashboard.
Appreciate in advance any ideas.
Answers
-
Hey @jmacdal!
Quick question while I look at this for you - what STATUSES do you have that you DON'T want calculated in this formula ("Complete", etc) - if it's not very many, it may be more efficient to make a negative statement in the formula rather than mention all the other statuses.
Let me know, and I'll be getting back to you either way!
-Jon
-
Got it done for you, the issue with your posted formula was the "+" in the "TODAY(+7)" portion - in the future just use "TODAY(7)". The below formula will calculate all (3) of those Statuses as well.
=COUNTIFS([Row Type]:[Row Type], "Task", Status:Status, OR(@cell = "Not Started", @cell = "In Progress", @cell = "On Hold"), [Due Date]:[Due Date], AND(@cell <= TODAY(7), @cell >= TODAY()))
Let me know if that solves the problem or if you have any other needs!
-Jon
-
Hey Jon - that formula is returning #UNPARSEABLE. Great catch on where to place the 7, I ran a test on just that part of the formula and it returned a total but it included tasks that were "Complete" or Canceled"? So now the issue is upstream from the Due Date section of the formula.
-
The + won't throw the error.
The error is coming from the [Due Date]:[Due Date] reference itself. Double check you have spelled it to exactly match the column name in your sheet. One thing to keep in mind... Smartsheet will store multiple consecutive spaces but will only display a single space.
My tip is to click and drag a few cells within the column when creating that reference so you end up with something like this:
[Due Date]1:[Due Date]5
Then just delete the specific row numbers. This will guarantee that your column name is a good match.
-
The Due Date must have had a space or something as is not working, but it took me to do the steps that Paul mentioned as well, strange but again it's working. Appreciate both of your help!
-
Happy to help. 👍
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives