Determine how many projects are active during given period

Michaela Kamenska

Hi all,

I was wondering if anyone can help? I've got quite a number of projects running from Timepoint A to Timepoint B (several months) and wondered if it was possible to write a formula in my (separate or the same sheet) metrics sheet to determine how many projects are active during given month?

For instance, I have 5 projects starting in March 2021 and another 14 starting in April 2021. I'd like to know how many total would be active if each project runs for exactly 2 months? Id est, if I have 5 projects starting in March and another 14 in April, I know that 19 projects are active/running in April, 19 projects are active in May and 14 would be active in June). This is my layout:

The current formula I have is only for counts of projects starting in a given month but not telling me how many are active in total (starting or in progess) next month:

=COUNTIFS({Ref 1}, IFERROR(YEAR(@cell), 0) = 2021, {Ref 1}, IFERROR(MONTH(@cell), 0) = 1)

Thanks in advance.

Paul Newcome

You are going to want to compare start and end date. Start date being less than the high range and end date being greater than the low range.

=COUNTIFS({Start Date}, @cell <= DATE(2021, 04, 30), {End Date}, @cell >= DATE(2021, 04, 01))

The above will tell you how many projects are active in April 2021.

Paul Newcome

You are going to want to compare start and end date. Start date being less than the high range and end date being greater than the low range.

=COUNTIFS({Start Date}, @cell <= DATE(2021, 04, 30), {End Date}, @cell >= DATE(2021, 04, 01))

The above will tell you how many projects are active in April 2021.

Michaela Kamenska

Hi @Paul Newcome ,

thanks a lot, everything works perfect, as usual!

You're awesome! :)

Paul Newcome

Happy to help. 👍️