Schedule Variance (Days) in Excel: Track Project Delays and Improve On-Time Performance

How to Build Schedule Variance (Days) Views in Excel

1. Calculate the Variance Column

• Start with the two date fields used in the video:
  • Planned End Date
  • Actual End Date
• Create a new Variance column by subtracting the planned end date from the actual end date.
• This gives you the number of days late for each project or job.
• In the example shown, the minimum value is 0, meaning no jobs finished early in this sample dataset.
• Once the variance column is created, it becomes the main field used throughout the rest of the analysis.

2. Create Unique Lists for System Type and PM

• Use the UNIQUE function to generate a list of all System Types.
• Sort the list alphabetically so it is easier to read and report on.
• Copy and paste the results as values using Ctrl + Shift + V.
• Repeat the same process for the PM list.
• Transpose the PM list so the names run across the top row instead of down a column.
• This creates the structure needed for a matrix view by System Type and PM.

3. Build the Average Schedule Variance Matrix

• Use AVERAGEIFS to calculate the average variance by:
  • PM
  • System Type
• Set the variance column as the value being averaged.
• Add PM as one criteria and System Type as the second criteria.
• Lock the row and column references correctly so the formula can be copied across and down.
• Copy and paste the completed formula across the full matrix.
• This first matrix shows the overall average variance, including rows where the variance is zero.

4. Create a Late-Jobs-Only Version

• Copy the first average matrix to create a second view.
• Add one more condition in AVERAGEIFS so it only includes variance values greater than zero.
• This removes on-time jobs from the average and shows only the average lateness for jobs that were actually late.
• Copy and paste the formula across the full section.
• This gives you a more focused view of delay severity by PM and System Type.

5. Add an Overall Average by System Type

• Copy the same average logic into a separate column for an overall summary.
• Remove the PM condition so the calculation is based only on System Type.
• Copy the formula down the list.
• This produces an overall schedule variance by system type, independent of project manager.
• Format the results as needed so they align with the rest of the dashboard.

6. Create a Chart for Schedule Variance by System Type

• Highlight the System Type column and the Overall Days column.
• Go to Insert and choose Recommended Charts.
• Select a chart style that compares categories clearly, such as a bar or column chart.
• Rename the chart title to match the report view.
• Sort the data from largest to smallest so the highest schedule variance appears first.
• Turn on Data Labels so the exact values appear directly on the chart.
• Adjust the number formatting to reduce decimals if needed.

7. Build a Days-by-PM Distribution Table

• Create a list of unique variance day values using SORT and UNIQUE.
• Copy and paste the results as values.
• Place the day values down the first column of a new table.
• Use COUNTIFS to count how many jobs each PM had at each specific variance day count.
• Add one condition for the variance day and another for the PM.
• Then divide that count by the total number of jobs for that PM to calculate the percentage distribution.
• Lock references correctly before copying the formulas across and down.
• This shows how often each PM finishes on time, 1 day late, 2 days late, and so on.

8. Combine Percentage and Job Count in One Cell

• Use the percentage result and the job count result together in one display field.
• Apply the TEXT function so the percentage displays in a readable format.
• Join the percentage and the job count into one combined label.
• Copy the formula across the row for all PMs.
• This creates a more useful summary because it shows both:
  • The percentage
  • The actual number of jobs behind that percentage

9. Build the Overall Count, Percent of Total, and Running Total View

• Use COUNTIF to count how many times each variance day appears overall.
• Divide each count by the total count to calculate the Percent of Total.
• Lock the total reference before copying the formula down.
• Create a Running Total column by adding each row’s percentage to the cumulative percentage above it.
• This view helps show how quickly jobs accumulate within a certain number of late days.
• In the video example, this makes it easy to see what percentage of jobs were completed within a small number of days from the planned finish date.