On-Time Arrival Rate by Time of Day and Technician

On Time Arrival Rate by Technician and Benchmark

1. Create the five helper columns in your dataset

• Add formulas to generate the fields you’ll use for analysis:
  
  • Scheduled Window Bucket based on the hour of the scheduled start time
    
    • Use HOUR() to extract the hour in 0–24 format
      
    • Use an IFS() ladder to map hours into buckets (7–9, 9–11, 11–1, 1–3, 3–5, After Hours)
      
  • Arrival Status using an IFS() that compares actual arrival to the scheduled window end
    
    • On Time if arrival is before the scheduled window end
      
    • 0–15 Late if arrival is within 15 minutes after the scheduled window end
      
    • 15–30 Late if arrival is within 30 minutes after the scheduled window end
      
    • 30+ Late otherwise
      
  • On Time Flag as a simple yes/no (binary) based on whether arrival is before the scheduled window end
    
  • Minutes Early as the difference when arrival is before the scheduled window start
    
  • Minutes Late as the difference when arrival is after the scheduled window end
    

2. Create a clean list of Arrival Status values and reorder them

• Use UNIQUE() on the Arrival Status column to generate a distinct list
  
• Copy and paste values (Ctrl+C then Ctrl+Shift+V) so you can rearrange
  
• Manually reorder the list into the sequence you want (so it reads logically in the table)
  

3. Create a clean list of Scheduled Window Buckets and put them across the top

• Use UNIQUE() on Scheduled Window Bucket
  
• Copy and paste values so you can reorder
  
• Reorder the buckets sequentially (7–9, 9–11, 11–1, 1–3, 3–5, After Hours)
  
• Transpose the bucket list across the top row (Paste Special → Transpose)
  

4. Build the percent table using COUNTIFS divided by COUNTIF

• In the first matrix cell, calculate the job count for a specific bucket and arrival status:
  
  • Use COUNTIFS() with:
    
    • Criteria 1: Scheduled Window Bucket equals the column header bucket
      
    • Criteria 2: Arrival Status equals the row label status
      
• Convert that count into a percentage by dividing by total jobs in that time bucket:
  
  • Use COUNTIF() for total jobs in the bucket
    
  • Divide: COUNTIFS(…) / COUNTIF(…)
    
• Format as percent
  

5. Fix drag behavior with absolute and relative references

• Update references so the formula works when dragged across and down:
  
  • The time bucket header should change left-to-right, but stay fixed when dragging down
    
  • The arrival status label should change top-to-bottom, but stay fixed when dragging across
    
• Use $ placement or F4 to lock:
  
  • Entire ranges for the dataset columns
    
  • Row-only or column-only locks for headers and labels
    
• After reference locking is correct:
  
  • Drag the formula across all time buckets
    
  • Drag the formula down all arrival statuses
    

6. Apply conditional formatting for the heatmap

• Select the matrix of percentages
  
• Apply Conditional Formatting → Color Scales
  
• Use a scale where high values are green and low values are red
  

7. Add total job counts by time bucket

• Pull the total jobs per time bucket using the denominator portion (the COUNTIF() result)
  
• Copy across to fill totals for each bucket without rebuilding formulas
  

8. Build On Time Arrival Rate by Technician

• Create a unique list of technicians using UNIQUE() on the Technician column
  
• Copy and paste values so you can sort later
  
• Calculate each technician’s on-time rate:
  
  • Numerator: COUNTIFS(TechnicianRange, TechnicianName, ArrivalStatusRange, “On Time”)
    
  • Denominator: COUNTIF(TechnicianRange, TechnicianName)
    
  • Divide numerator by denominator and format as percent
    
• Fill down for all technicians
  

9. Add a benchmark line and sort technicians

• Create a Benchmark column with a fixed value (example shown: 85%)
  
• Sort the technician on-time rates Largest to Smallest (Data → Sort Z to A)
  
• After sorting, fix any formulas that did not carry correctly, then fill down again
  
• Populate the benchmark column down the full list
  

10. Create the combo chart with technician bars and benchmark line

• Select Technician names, On Time Arrival Rate, and Benchmark
  
• Insert → Combo chart
  
  • On Time Arrival Rate as a Clustered Column
    
  • Benchmark as a Line
    
• Add data labels to the columns
  
• Adjust the benchmark line thickness so it is visible but not overpowering
  
• Name the chart based on the KPI (example shown: OnTimeArrivalRateByTechnician)