How to Track Permit and Inspection Delay Rate in Excel for Light Commercial Projects

How to Track Permit / Inspection Delay Rate % in Excel

1. Filter the milestone data to only the permit and inspection stages

• Go to the milestone data tab and identify the milestone names related to permitting and inspection.
• Keep only the three milestone stages used in the analysis:
  • Permit Submitted
  • Permit Approved
  • Inspection
• Filter the dataset to those milestones only.
• Copy those milestone names into your reporting section so you can build the KPI table from them.
• Label that first column something like Milestone.

2. Create helper columns to identify delayed records and late days

• Add one helper column to flag whether each milestone was delayed.
• Use an IF function to compare the actual milestone date against the planned date.
• Mark delayed records so they can be counted separately from on-time records.
• Add a second helper column to calculate the number of days late.
• Use a simple date subtraction so each delayed milestone shows how many days it was late.

These two helper columns make the rest of the KPI much easier to calculate.

3. Count how many times each permit or inspection milestone appears

• In your summary table, count how many records exist for each of the three milestones.
• Use COUNTIF on the milestone name field.
• This gives you the total number of opportunities for delay at each stage.

This becomes the denominator for the delay rate calculation.

4. Count how many of those milestones were delayed

• Use COUNTIFS to count only the records that meet both conditions:
  • The milestone matches the row label
  • The delayed flag shows it was late
• Copy the formula down for all three milestones.

This gives you the delayed count for each stage.

5. Calculate the delay rate percentage

• Divide the delayed count by the total count for each milestone.
• Format the result as a percentage.
• Add decimals only if you want a more detailed display.

This gives you the delay rate for Permit Submitted, Permit Approved, and Inspection.

6. Add a target line for comparison

• Create a target column next to the delay rate.
• Enter the same target percentage for each milestone.
• Make the target values reference a single input cell if you want the chart to update dynamically whenever the target changes.

This makes it easier to compare actual delay performance against a benchmark.

7. Calculate the average number of late days

• Use AVERAGEIFS to calculate the average late days for each milestone.
• Average the late-day helper column only where:
  • The record was delayed
  • The milestone matches the row label
• Copy the formula down for all three milestones.

This shows not just how often delays happen, but how severe they are.

8. Build the high-level chart

• Highlight the milestone names, delay rate percentages, and target values.
• Insert a Combo Chart.
• Use:
  • A column chart for the delay percentages
  • A line chart for the target
• Sort the table from largest to smallest if you want the most delayed milestone to appear first.

This creates a high-level visual showing which stage has the biggest delay problem.

9. Clean up the chart formatting

• Rename the chart to match the KPI, such as Permit / Inspection Delay Rate % for Light Commercial.
• Remove unnecessary decimal places from the percentage labels.
• Add data labels to the columns only, not the target line.
• Adjust label formatting to make the chart easier to read.

This gives you a cleaner executive-level view.

10. Build a more detailed breakdown by project manager

• Copy the same three milestone names into a second section.
• Create a unique list of project managers using the UNIQUE function.
• Sort the list alphabetically.
• Transpose the names across the top row so each project manager becomes a column heading.
• Paste values to remove formulas if needed.

This sets up a matrix where you can compare delay rates by milestone and by project manager.

11. Calculate project manager level delay rates

• Use COUNTIFS to count delayed milestones by:
  • Project manager
  • Milestone name
  • Delayed flag
• Then divide by the total count for that same project manager and milestone combination.
• Use absolute cell referencing so the formulas copy correctly across rows and columns.
• Format the result as a percentage.

This shows which project managers are above or below average for each permit and inspection stage.

12. Build a second breakdown by system type

• Create another section using the same three milestone names.
• Build a unique list of system types using UNIQUE.
• Sort the list and transpose it across the columns.
• Paste values to lock in the headings.

This lets you analyze delays by equipment or project type instead of by person.

13. Calculate system type level delay rates

• Use COUNTIFS again to count delayed records by:
  • System type
  • Milestone name
  • Delayed flag
• Divide by the total count for that same milestone and system type.
• Format as percentages and copy the formulas across the grid.

This gives you a second lens into the KPI and helps identify whether certain system types create more permitting or inspection risk.

14. Add counts to validate sample size

• Pull in the denominator count for each system type or project manager grouping.
• Reuse the denominator portion of your earlier logic instead of rebuilding it from scratch.
• Display the count once for each grouping so you can quickly judge whether the percentages are based on enough volume.

This helps separate real patterns from small-sample noise.

15. Apply a heat map to make problem areas stand out

• Highlight the detailed matrix.
• Go to Conditional Formatting and apply a color scale.
• Use a format where higher delay rates are more visually prominent.

This makes it much easier to spot which milestones, project managers, or system types are creating the most delay risk.

16. Use the KPI at multiple levels of detail

• Start with the high-level milestone chart to see where delay is happening most often.
• Then use the project manager matrix to compare operational patterns across PMs.
• Finally, use the system type matrix to see whether the work itself is contributing to delay frequency.

This layered setup gives you both the summary view and the granular detail needed to investigate the problem properly.