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
- 66.6K Get Help
- 434 Global Discussions
- 153 Industry Talk
- 495 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 508 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I have a column that is a contact list where a single or multiple staff members can be selected to be scheduled. I'm trying to create a formula that counts how many times each individual's names are l…
- I have two columns I am using for this formula: "Date Received" and "Days Duration (Received to Listed)". I want the formula below to look at any items in the Date Received column between 1/1/25 and 1…
- Hello, I am trying to create a report or calendar sheet with these weekly rotating roles: Weekly 1st shift Call Center (Mon-Sun) Weekly 2nd shift Call Center (Mon-Sun) Weekend On-Call 1 (Sat-Sun) Week…