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

08/06/20
Answered - Pending Review

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.



Answers

  • MCorbinMCorbin Overachievers

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

  • MCorbinMCorbin Overachievers

    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 FletcherCorey Fletcher ✭✭✭✭✭
    edited 08/06/20

    I have that flag in this sheet already.



  • Mike WildayMike 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.

  • MCorbinMCorbin Overachievers

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

Sign In or Register to comment.