Counting active projects by quarter

COLOF

Hi 

I need to count the number of projects that are active in each quarter by business area. I have a start and finish date. The will run over multiple quarters. 

I have used this formula but this seems to only pick up what is active in this date bracket but does not include the projects that for example run from 12/1/19 - 06/06/20

=COUNTIFS({RIR Range 4}, 1, {RIR Range 3}, @cell <= DATE(2020, 1, 1), {RIR Range 2}, @cell >= DATE(2020, 3, 31))

Paul Newcome

Try something like this for your date ranges...

 

=COUNTIFS({RIR Range 4}, 1, @{RIR Start Date}, @cell <= DATE(2020, 3, 31), {RIR End Date}, @cell >= DATE(2020, 1, 1))

.

Basically you want to compare the Project Start Date to the Quarter End Date and the Project End Date to the Quarter Start Date.