Milestone OTIF metric

Angie Saunders PMP

I've been given a new KPI of on time and in full (two different mesurables) for project milestones and I'm not sure how to best track the "on time" part of the milestones. How would you figure the % of milestones that are hit on time? It feels like it would need to be very manual.

Paul Newcome

In that case, you would use a helper column (can be hidden after setting up) with a formula that indicates whether it was on time or late.

=IF([Actual Date]@row <> "", IF([Actual Date]@row<= [Target Date]@row, "On Time", "Late"))

Then to get the percentage that are "On Time" you would use

=COUNTIFS([Helper Column]:[Helper Column], @cell = "On Time") / COUNTIFS([Helper Column]:[Helper Column], @cell <> "")

Paul Newcome

Do you have a date column for the target completion and another date column for the actual completion?

Angie Saunders PMP

https://community.smartsheet.com/discussion/comment/409080#Comment_409080

Yes sir

Paul Newcome

In that case, you would use a helper column (can be hidden after setting up) with a formula that indicates whether it was on time or late.

=IF([Actual Date]@row <> "", IF([Actual Date]@row<= [Target Date]@row, "On Time", "Late"))

Then to get the percentage that are "On Time" you would use

=COUNTIFS([Helper Column]:[Helper Column], @cell = "On Time") / COUNTIFS([Helper Column]:[Helper Column], @cell <> "")

Angie Saunders PMP

https://community.smartsheet.com/discussion/comment/409293#Comment_409293

Would you use the following to find the % On Time for Milestones (MR) only

=COUNTIFS([Helper Column]:[Helper Column], @cell = "On Time") / COUNTIFS([Helper Column]:[Helper Column], @cell <> "") AND COUNTIFS([MR]:[MR], @cell = "TRUE"

Paul Newcome

You would include that range/criteria set in both of the already existing COUNTIFS. You would not use a third COUNTIFS.

Angie Saunders PMP

You are AWESOME thank you

https://community.smartsheet.com/discussion/comment/409310#Comment_409310

Paul Newcome

Happy to help. 👍️