How to count the tasks that are past due, due in the next 7 days and in the next 3 days?

Lea M.

I have a Due date column and I would like to count how many tasks are due in the next 3 days, due in the next seven days and overdue tasks. Kindly help me on the formula.

Overdue= =COUNTIFS({Status Range 1}, "Open", {Due Date Range 2}, <TODAY())

Due in the next 7 days= =COUNTIFS({Status Range 1}, "Open", {Due Date Range 2}, >=TODAY(7))

Due in the next 3 days=COUNTIFS({Status Range 1}, "Open", {Due Date Range 2}, >=TODAY(3))

Heather Duff

Hi Lea,

You're super close! Here you go!

Overdue= =COUNTIFS({Status Range 1}, "Open", {Due Date Range 2}, <TODAY())

Due in the next 7 days= =COUNTIFS({Status Range 1}, "Open", {Due Date Range 2}, <=TODAY(+7))

Due in the next 3 days=COUNTIFS({Status Range 1}, "Open", {Due Date Range 2}, <=TODAY(+3))

Note that the last two formulas will still count the number of items that are past due and open as well, so if you want to rule those out, add

, {due date range 2}, <TODAY()

to each due date formula. Then they would read:

Due in the next 7 days= =COUNTIFS({Status Range 1}, "Open", {Due Date Range 2}, <=TODAY(+7), {due date range 2}, <TODAY())

Due in the next 3 days=COUNTIFS({Status Range 1}, "Open", {Due Date Range 2}, <=TODAY(+3), {due date range 2}, >TODAY())

Best,

Heather

Heather Duff

Hi Lea,

You're super close! Here you go!

Overdue= =COUNTIFS({Status Range 1}, "Open", {Due Date Range 2}, <TODAY())

Due in the next 7 days= =COUNTIFS({Status Range 1}, "Open", {Due Date Range 2}, <=TODAY(+7))

Due in the next 3 days=COUNTIFS({Status Range 1}, "Open", {Due Date Range 2}, <=TODAY(+3))

Note that the last two formulas will still count the number of items that are past due and open as well, so if you want to rule those out, add

, {due date range 2}, <TODAY()

to each due date formula. Then they would read:

Due in the next 7 days= =COUNTIFS({Status Range 1}, "Open", {Due Date Range 2}, <=TODAY(+7), {due date range 2}, <TODAY())

Due in the next 3 days=COUNTIFS({Status Range 1}, "Open", {Due Date Range 2}, <=TODAY(+3), {due date range 2}, >TODAY())

Best,

Heather

Lea M.

Thank you Heather, it works.