# Milestone OTIF metric

edited 12/11/23

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.

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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

• 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"

• ✭✭✭✭✭✭

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

• You are AWESOME thank you

• ✭✭✭✭✭✭

Happy to help. 👍️