Formula Help

karizimmeran

Hi - I need help with a formula for a sheet summary field. 1. Open Tasks that are due in the next week. I want to capture a status that does not contain "Complete" and then if the task is within the next 7 days.

2. I also want to capture the count of open tasks that are overdue - i.e., in the past.

Thank you!

Melissa Yamada

Hello @karizimmeran

Try:

Open Tasks Due in next 7 Days:

=COUNTIFS([Task Status Column]:[Task Status Column], <> "Complete", [Due Date Column]:[Due Date Column], AND(@cell <= TODAY(+7), @cell >= TODAY()))

Overdue Tasks:

=COUNTIFS([Task Status Column]:[Task Status Column], <> "Complete", [Due Date Column]:[Due Date Column], @cell < TODAY())

Melissa Yamada

Hello @karizimmeran

Try:

Open Tasks Due in next 7 Days:

=COUNTIFS([Task Status Column]:[Task Status Column], <> "Complete", [Due Date Column]:[Due Date Column], AND(@cell <= TODAY(+7), @cell >= TODAY()))

Overdue Tasks:

=COUNTIFS([Task Status Column]:[Task Status Column], <> "Complete", [Due Date Column]:[Due Date Column], @cell < TODAY())

karizimmeran

Hi Melissa - That worked! Thank you so much!!

Melissa Yamada

Happy to help! 😊