Counting active projects by quarter

COLOF
COLOF
edited 12/09/19 in Formulas and Functions

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))

Tags:

Comments

  • Paul Newcome
    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!