How to retain monthly calculated counts of projects in certain statuses?

Steven Munsey

We need a way to provide counts of projects in various statuses each month through a calendar year; for example...how many projects were in progress in the month of Jan, how many projects were completed in the month of Feb, etc.

I have a column for each month in a calendar year and rows representing the metrics we need to capture (see below image). The issue is that project statuses change over time; a project that was in progress in Jan and completed in Jun was technically in progress Jan, Feb, Mar, Apr, May and should be counted as such. The data should count that project as being "in progress" each month but, once the status of that project changes to "completed" in Jun, the counts in the previous month decrease because the status is no longer "in progress".

Today, I use formulas to count everything "in progress" for each metric and then copy/paste the values each week in that month; This is a manual effort and I would love to automate but am struggling with how to do this. I may be able to use the new Record Date workflow functionality to capture the dates when a project goes to "in progress" and leaves "in progress" but what would the formula look like to calculate and retain that count each month? It can't be based on the current status of the project...it has to be based on the status during that month. I basically need a "snapshot" of the data for each month. Please help...any and all ideas appreciated!

Bassam Khalil

Hi @Steven Munsey 

Hope you are fine, could you please supply the screenshot you mentioned in your question ( please remove any sensitive data )

Andrée Starå

Hi @Steven Munsey

Yes, I agree that the record date would be the way to go.

Something like this.

My idea would be to store the date it changes to In Progress and then uses that in combination with the TODAY function, so if the Status is in progress and if the month is in the range from today to the recorded in progress start date, it would count for that month.

Make sense?

Would that work/help?

I hope that helps!

Have a fantastic day & Happy New Year!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Steven Munsey

Thanks for the prompt replies! I think we are getting closer :)

So, here is an example of the formula I am using to calculate how many new install projects completed in a given month: =COUNTIFS({Work Type}, "New Install", {Completion Date}, AND(@cell >= DATE(2021, 1, 1), @cell <= DATE(2021, 1, 31)))

This works well because the status of "completed" is actually based on a specific date (Completion Date) that never changes; not so with a status of "in progress" since a project can be in progress for several months. Using the new record date when a project status changes to "in progress" option, is there an example of what the formula would look like for each month so that the count doesn't decrease in previous months as soon as the status of a project changes to "completed"?

BTW, here is the screenshot of my sheet again showing my columns and rows. There is a formula in each cell for each month that is performing the counts.

In this scenario, we currently have 20 projects "in progress" in the month of Jan. Come Feb, Mar, etc., I need to ensure that the Jan count of 20 doesn't change if one one of those 20 projects is completed in a future month. Even though the in progress start date is Jan, the in progress end date may be in May. I will need the In Progress row counts in each month to show "20" until May when the count will be "19". If my formula includes verbiage about the status of "in progress", and the status of a project changes to "completed" in May, the formula in the cells for Jan, Feb, Mar, etc. will no longer count that project as in progress. I just really need that data in the cell to "lock" or become static once the month of Jan is done. Whew, this is complicated, sorry to be so long winded.

plaistedcj

Does anyone have a formula for the above post? I am trying to calculate the same as Steven.