COUNTIFS formula for tasks due between 8 and 14 days in future

Lori Flanigan

I'm trying to write a formula to count the number of tasks due between 8 and 14 days in the future. I started with a formula that is working for the number of tasks due in 7 days:

=COUNTIFS({Schedule - % Complete}, <1, {Schedule - End Date}, <=TODAY(+7))

I could repeat the formula, replacing the +7 with +14, but I don't want the tasks due in 7 days to be repeated in the due-in-14-days number. I tried this formula, but I get invalid operation.

=COUNTIFS({Schedule - % Complete}, <1, {Schedule - End Date}, >TODAY(+7), <=TODAY(+14)).

Thank you for any help you can provide.

Lori Flanigan

Sean Morgan

Hello @loreleijflanigan112461 ,

I was able to create an example Formula that should provide you with the desired result. Please note that where I have Column Name References, you would instead use your Sheet Reference Names.

The Formula I used was: =COUNTIFS([Schedule - % Complete]:[Schedule - % Complete], <1, [Schedule - End Date]:[Schedule - End Date], >=TODAY(+7), [Schedule - End Date]:[Schedule - End Date], <=TODAY(+14))

Here's the Output of this Formula:

When using date ranges within a Formula, you must reference the Date Range for both the Greater Than, and the Less Than " [Schedule - End Date]:[Schedule - End Date], >=TODAY(+7), [Schedule - End Date]:[Schedule - End Date], <=TODAY(+14))"

Let me know if you have any questions!

Regards

Sean

Lori Flanigan

Thanks for responding, Sean. I entered the example formula you provided with my sheet reference names. I'm getting an #UNPARESEABLE error message.

Lori

Sean Morgan

Hey @loreleijflanigan112461 ,

Are you able to provide screenshots of what you are currently seeing, whilst removing any sensitive data?

Kindest Regards

Sean

Lori Flanigan

We got the formula to work. Here is the final version:

=COUNTIFS({Schedule - % Complete}, <1, {Schedule - End Date}, >=TODAY(+8), {Schedule - End Date}, <=TODAY(+14))

Thanks!

Lori

Sean Morgan

@loreleijflanigan112461

Awesome!

Glad its working now

Regards

Sean

1Andi

Hello, I'd like to use this formula in a Summary field, but it seems to be including weekends. I have excluded Sat/Sun in the schedule, but how do I exclude weekends in this formula?

Genevieve P.

Hi @1Andi

You're right, the date range of 8 - 14 days is 7 days through a weekend, dependent on Today's date (even if that day is a Saturday or Sunday).

For your formula, can you clarify what your sheet set-up is like (screen captures would be ideal), and what exactly you're looking to count?

1Andi

Thank you for the quick response. I'm trying calculate the number of tasks that (1) have a have a value of "Ops" in one column, (2) have only open statuses, and (3) have an end date in another column that are between two days in the future. In this case, counts of Ops tasks due between 5-10 days from now. My formula works at day 6 but then stops. It doesn't include days 7-10. And, I would like to exclude weekends.

=COUNTIFS(Status:Status, <>"Complete", Status:Status, <>"Cancelled", Stream:Stream, "OPS", [End Date]:[End Date], >=TODAY(6), [End Date]:[End Date], <=TODAY(11))

Genevieve P.

Hi @1Andi

It looks like you're close! What I would do here is use the WORKDAY function instead of just TODAY(days).

For example:

=COUNTIFS(Status:Status, <>"Complete", Status:Status, <>"Cancelled", Stream:Stream, "OPS", [End Date]:[End Date], >=WORKDAY(TODAY(), 6), [End Date]:[End Date], <=WORKDAY(TODAY(), 11))

This should count the rows that have a date equal-to-or-between 6 - 11 working days, based on today's date. Let me know if that worked for you!

Cheers,

Genevieve

1Andi

That fixed it - thank you!!!