Rolling 12-months formula: % of projects completed on time

Corey Fletcher

I am looking for a formula that will calculate the % of projects completed on time when the actual complete date is on or before the scheduled complete over a rolling 12-month period and the Project Status is Completed. The rolling 12-months will be based on the Actual Complete Date column. Below is is the data. So for the data below it should return a value of 70% because 7 of 10 projects had an actual complete date on or before the scheduled complete date.

MCorbin

This formula will get you your % completed on time:

=COUNTIFS(Status:Status, "Completed", [Finish Date]:[Finish Date], <=[Scheduled Finish]@row) / COUNTIFS(Status:Status, "Completed")

When I do a rolling 12 months metric, I create a metric sheet like this. I have a support table that gives me the last day of the month for every month to make my formulas easier :-)

Since this then turns your formula into a Cross-Sheet Formula, I'd add a Helper Column to the Source Sheet that calculates if the Finish Date is <=Scheduled Finish (Mine gives me a 1 if it finished on time, 0 if it didn't)

So to get your % Complete for each month then, you would modify the above formula

=COUNTIFS({Status}, "Completed", {On Time}, 1, {Finish Date}, >=DATE([11 Months Ago]$3, [11 Months Ago]$4, 1), {Finish Date}, <=DATE([11 Months Ago]$3, [11 Months Ago]$4, [11 Months Ago]$5))

MCorbin

I should add... if you set up your metrics sheet with the helper rows like mine, you can then copy and paste this formula across your columns, and it will work for each month.

Corey Fletcher

I have that flag in this sheet already.

Mike Wilday

@MCorbin Can you share a copy of your support table or give us an example of how you put that together? This is an interesting concept and I am trying to help another user with this concept.

MCorbin

@Mike Wilday

Here's the support table:

It's all data entered. The Quarter Start and End dates are set to always default to current year

My "Last Day" formula (in the rolling 12 month sheet) checks to see if it's a leap year and pulls the leap year last day if the month # is 2

=IF(AND([11 Months Ago]$4 = 2, COUNTIF({Leap Years}, [11 Months Ago]3) > 0), 29, INDEX({Last Day}, MATCH([11 Months Ago]$4, {Month Number}, 0)))

(this is the formula from the "11 months ago" column)