Formula Help
Hi - I need help with a formula for a sheet summary field. 1. Open Tasks that are due in the next week. I want to capture a status that does not contain "Complete" and then if the task is within the next 7 days.
2. I also want to capture the count of open tasks that are overdue - i.e., in the past.
Thank you!
Best Answer
-
Hello @karizimmeran
Try:
Open Tasks Due in next 7 Days:
=COUNTIFS([Task Status Column]:[Task Status Column], <> "Complete", [Due Date Column]:[Due Date Column], AND(@cell <= TODAY(+7), @cell >= TODAY()))
Overdue Tasks:
=COUNTIFS([Task Status Column]:[Task Status Column], <> "Complete", [Due Date Column]:[Due Date Column], @cell < TODAY())
Melissa Yamada
melissa@insightfulsheets.com
Data made simple, spreadsheets reimagined
Answers
-
Hello @karizimmeran
Try:
Open Tasks Due in next 7 Days:
=COUNTIFS([Task Status Column]:[Task Status Column], <> "Complete", [Due Date Column]:[Due Date Column], AND(@cell <= TODAY(+7), @cell >= TODAY()))
Overdue Tasks:
=COUNTIFS([Task Status Column]:[Task Status Column], <> "Complete", [Due Date Column]:[Due Date Column], @cell < TODAY())
Melissa Yamada
melissa@insightfulsheets.com
Data made simple, spreadsheets reimagined -
Hi Melissa - That worked! Thank you so much!!
-
Happy to help! 😊
Melissa Yamada
melissa@insightfulsheets.com
Data made simple, spreadsheets reimagined
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!