Formula to track projects running per quarter

212706995

Hi,

I have a table with a list of projects (project ID available) and related columns with Start Date and End Date each project (column in date format).

I want to be able to create reports and display metrics in the Dashboard showing up how many active projects I have running during a given quarter (considering Start date).

And how many project I have go live planned in a given quarter (End Date).

I was thinking to create help columns in my table, representing each quarter  and to populated them with a formula considering…

Help columns Example: Start date: SQ1, SQ2, SQ3 and SQ4/ End date: EQ1, EQ2, EQ3, EQ4.

Formula rule: If a project have a Start date less than MAR, 31 , populate a “Yes” in the column SQ1.

If a project have an End Date less than MAR, 31, populate a “Yes” in the column EQ1.

Expectation is to finally be able to count how many projects I have running in the SQ1.  

And do the same with End date, to verify how many projects have go live date planned in a given quarter also.

I am not sure if this is the best way and what formula should I use.

Look forward to seeing possible solutions. Appreciated any help!

Paul Newcome

You could use a COUNTIFS directly on the date columns.

=COUNTIFS({Date Column}, AND(@cell>= DATE(2023, 01, 01), @cell<= DATE(2023, 03, 01)))