Calculate upcoming tasks

Silvia Frei

Hi,

I am trying to calculate the number of upcoming tasks in the next 15 days.

This formula works BUT the number returned is not correct. Any idea why?

=COUNTIFS(Milestone:Milestone, CONTAINS("V2", @cell), [Published due date]:[Published due date], =TODAY(+15))

"V2" is in a text column with no other text in it

I have tried using HAS instead but I get the same number returned.

Below an example, there should be at least 6 tasks returned but I get 5 with the formula above

Paul Newcome

Your first one doesn't include greater then today and your second one is back to being equals.

Try this one from before.

=COUNTIFS(Milestone:Milestone, CONTAINS("SS", @cell), [Published due date]:[Published due date], AND(@cell >= TODAY(), @cell <= TODAY(15)))

=COUNTIFS(Milestone:Milestone, CONTAINS("SS", @cell), [Published due date]:[Published due date], AND(@cell >= TODAY(), @cell <= TODAY(30)))

Paul Newcome

In your above screenshot I only see 6 rows that match your criteria. The row containing "Pricing+" is "SS".

Silvia Frei

Thanks Paul, good point. Assuming that's correct (which may be!) when I apply the same formula but to calculate upcoming tasks in next 30 days, I get 0 returned.. which doesn't seem right..

=COUNTIFS(Milestone:Milestone, CONTAINS("SS", @cell), [Published due date]:[Published due date], =TODAY(+30))

Paul Newcome

Are you trying to get all tasks within the next 30 days or tasks that are 30 days out? Your formula currently says EQUALS 30 days in the future, but it seems as if you want everything LESS THAN OR EQUAL TO 30 days in the future.

=COUNTIFS(Milestone:Milestone, CONTAINS("SS", @cell), [Published due date]:[Published due date], @cell <= TODAY(+30))

Silvia Frei

I'm trying to get all tasks within the next 30 days

Paul Newcome

Then you are going to want to try the above. If that starts pulling in past dates since yesterday is technically less than 30 days in the future we can establish both ends of the range by stating less than or equal to +30 days AND greater than or equal to today (meaning not in the past).

=COUNTIFS(Milestone:Milestone, CONTAINS("SS", @cell), [Published due date]:[Published due date], AND(@cell >= TODAY(), @cell <= TODAY(+30)))

Silvia Frei

Thanks! It seems to work by using:

Next 15 days: =COUNTIFS(Milestone:Milestone, CONTAINS("V2", @cell), [Published due date]:[Published due date], =TODAY(+15))

Next 30 days: =COUNTIFS(Milestone:Milestone, CONTAINS("V2", @cell), [Published due date]:[Published due date], @cell <= TODAY(+30))

Paul Newcome

The Next 15 Days formula will only count those that are exactly 15 days away (= vs <=). So those rows that it is counting today will not be counted tomorrow.

Silvia Frei

Thanks Paul, I can see that now the Next 15 days formula is down to 0.

However if I use the Next 30 days formula I get the same result. So

=COUNTIFS(Milestone:Milestone, CONTAINS("SS", @cell), [Published due date]:[Published due date], @cell <= TODAY(+15))

=COUNTIFS(Milestone:Milestone, CONTAINS("SS", @cell), [Published due date]:[Published due date], @cell <= TODAY(+30))

Gives me the same result which is incorrect. Any suggestion?

Paul Newcome

Are you sure that you have rows that are more than 15 days in the future but less than 30?

Silvia Frei

I should have 1 item within the next 15 days and 3 in next 30 days, following the report that looks at all SS tasks

I don't get why it's giving me 9 as a results...

Paul Newcome

Try adding in the greater than today argument as I had above to exclude items in the past.

Silvia Frei

=COUNTIFS(Milestone:Milestone, CONTAINS("SS", @cell), [Published due date]:[Published due date], @cell <= TODAY(+15))

Gives me 9 as a result

=COUNTIFS(Milestone:Milestone, CONTAINS("SS", @cell), [Published due date]:[Published due date], @cell = TODAY(+15))

Gives me 0 as a result

Both incorrect 😕

Paul Newcome

Your first one doesn't include greater then today and your second one is back to being equals.

Try this one from before.

=COUNTIFS(Milestone:Milestone, CONTAINS("SS", @cell), [Published due date]:[Published due date], AND(@cell >= TODAY(), @cell <= TODAY(15)))

=COUNTIFS(Milestone:Milestone, CONTAINS("SS", @cell), [Published due date]:[Published due date], AND(@cell >= TODAY(), @cell <= TODAY(30)))

Silvia Frei

Works perfectly! Thanks for your patience on this! 🤗