Count What is Due for Today and the next 7 days

Andrea Westrich

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")

David Tutwiler

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

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".

David Tutwiler

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.

Andrea Westrich

@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.

Andrea Westrich

@David Tutwiler that worked perfectly!! Thank you so much, I would not have thought of that. I appreciate your help!!

David Tutwiler

No problem. I'm glad it's working

Gabriel Barrera

@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.

David Tutwiler

@Gabriel Barrera I'm glad this post was able to help.