How to get Month to Month Metrics by Project Phase

Jennifer Lewkowicz

Hi all,

I am trying to figure out a better way to gather metrics for my department's portfolio. We have 15 Project Portfolios that show various phases which we have been gathering the data manually by saving the metrics at the end of every month:

0-Request

1-Planning

2-In Process

3-Complete

4-Closed

For closed and complete, I've set up a Record a Date Workflow to capture their data as they are set dates, so that one is easy. But for the other three, as they are dynamic, and we need to capture the data dynamically for each month and put into a rolling 13 month chart for management. I am looking for a way to NOT do this manually. Any ideas?

I'm thinking maybe using Record a date in a hidden column to help but not sure how it would help with the dynamic aspect (but would help with end of month). I feel brain dead. Please help with any formulas or ideas.

Heather Duff

Hi Jennifer,

Please see my screenshot below:

I had to add a helper row (row 1) to create the rolling months. I then used the formulas in the color-coded boxes as listed on the right side, then drag-filled them to the right. For the bottom (yellow) formula, I drag-filled it to the right, then drag-filled everything to to the bottom.

The {status} range references the Status column in my source sheet, and the {dates} range references the Date column in my source sheet.

I know yours may be a bit more complicated, but hopefully this will give you a starting place with the rolling summary.

Best,

Heather