Count What is Due for Today and the next 7 days
I have a summary sheet where I want to count the incomplete tasks due by company for the next 7 days. The formula below counts 0 where there are three. If I remove the 7 it counts everything incomplete. What should I do with the TODAY to make it count tasks for today AND the next 7 days?
Thanks!
=COUNTIFS({Company}, [Company]@row, {Finish Date}, >=TODAY(7), {Completed}, "No")
Best Answer
-
I think the problem is in how you're managing your Finish Date. I don't think it is resolving as you think it is. It reads that it will require the Finish Date to be = or greater than 7 days from now. I think what you want is this:
=COUNTIFS({Company}, [Company]@row, {Finish Date}, AND(@cell >=TODAY(), @cell < TODAY(8)), {Completed}, "No")
This should put the date in the range you're looking for.
Answers
-
Hello, @Andrea Westrich . So the syntax appears to be correct, Is the "Finish Date" a date column, and is {Completed} a check box or text? If {Completed} is a check box, you should be checking for a '"0" instead of a "No".
Fernando Flores | M365 Enterprise Project Manager ✌️ 🍻
-
I think the problem is in how you're managing your Finish Date. I don't think it is resolving as you think it is. It reads that it will require the Finish Date to be = or greater than 7 days from now. I think what you want is this:
=COUNTIFS({Company}, [Company]@row, {Finish Date}, AND(@cell >=TODAY(), @cell < TODAY(8)), {Completed}, "No")
This should put the date in the range you're looking for.
-
@404.IRL thanks for replying. The "No" works for other things, so I know that is working. Like if I take away the 7 with TODAY, it will count all tasks with a No. But when I add the 7, it says 0 tasks. So I think it's something with the TODAY function. BTW, all of them are due today.
-
@David Tutwiler that worked perfectly!! Thank you so much, I would not have thought of that. I appreciate your help!!
-
No problem. I'm glad it's working
-
@David Tutwiler that was extremely helpful. I was racking my brain trying to figure it out. I didn't think about using an AND function.
-
@Gabriel Barrera I'm glad this post was able to help.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!