How to count newly added tasks and closed task for previous week.
Hello,
Can someone help me to create formula for my requirement.
Everyweek starting on Monday, I need to look for how many tasks were added and how many were closed in previous week.
I attached sample xls sheet. As we know everyweek some tasks will be opened and some tasks will closed.
I would like to see a summary of the tasks were added in the previous week and closed tasks in the previous week.
Thanks,
Muni
Answers
-
Hey @Muni Pavan Kumar Reddy Kona,
By utilizing a combination of the COUNTIFS, WEEKNUMBER, TODAY, NOT, and ISBLANK functions, we can evaluate if a task was newly created last week and if there were any closed tasks. I created the example below on how this may look like:
New Tasks Last Week (Formula): =COUNTIFS([Date Added]:[Date Added], NOT(ISBLANK(@cell)), [Date Added]:[Date Added], WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()) - 1)
- This formula counts if the Date in our range is not blank and if the Date's Week Number is equal to Today's Week Number minus 1 (last week).
Closed Tasks Last Week (Formula): =COUNTIFS([Date Added]:[Date Added], NOT(ISBLANK(@cell)), [Date Added]:[Date Added], WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()) - 1, Status:Status, "Closed")
- This formula counts if the Date in our range is not blank, if the Date's Week Number is equal to Today's Week Number minus 1 (last week) and the Status is "Closed".
I hope this helps!
Jaykel
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!