Average Service Call Duration in Excel
(With Target Min/Max)

Build Average Service Call Duration in Excel

1. Create a Month field from Call Date

• In your dataset, use the Call Date column (Column D).
  
• Create a Month column using the TEXT function:
  
  • =TEXT([Call Date],”mmmm”)
    
• Confirm it returns month names like January, February, etc.
  

2. Build the Month list on your report tab

• On your starting point tab, type Month as the header.
  
• Type January and drag down through December.
  

3. Calculate Total Service Minutes in the dataset

• Create a new column called Total Service Minutes.
  
• Subtract Work Start Time from Work End Time:
  
  • =[Work End Time] – [Work Start Time]
    
• Convert the result to minutes by multiplying:
  
  • =([Work End Time] – [Work Start Time]) * 24 * 60
    
• Fill the formula down the entire dataset.
  

4. Calculate Total Completed Minutes by month

• In your monthly table, create a column called Total Completed Minutes.
  
• Use SUMIFS to sum Total Service Minutes where:
  
  • Outcome equals “Completed”
    
  • Month equals the month in your row (January, February, etc.)
    
• Example structure:
  
  • Sum range: Total Service Minutes
    
  • Criteria 1: Outcome column equals “Completed”
    
  • Criteria 2: Month column equals the month label
    
• Copy the formula down for all months.
  
• Apply number formatting with thousands separators and center-align if desired.
  

5. Calculate Total Completed Jobs by month

• Create a column called Total Completed Jobs.
  
• Use COUNTIFS to count rows where:
  
  • Outcome equals “Completed”
    
  • Month equals the month in your row
    
• Copy down for all months.
  

6. Calculate Total Completed Part Ordered Minutes by month

• Create a column called Total Completed Part Ordered Minutes.
  
• Use SUMIFS again, but change the outcome criteria to:
  
  • “Completed – Part Ordered”
    
• Keep the Month criteria the same.
  
• Copy down for all months.
  

7. Calculate Total Completed Part Ordered Jobs by month

• Create a column called Total Completed Part Ordered Jobs.
  
• Use COUNTIFS where:
  
  • Outcome equals “Completed – Part Ordered”
    
  • Month equals the month in your row
    
• Copy down for all months.
  

8. Calculate Average Duration by month

• Create a column called Average Duration.
  
• Calculate it as:
  
  • (Completed Minutes + Part Ordered Minutes) ÷ (Completed Jobs + Part Ordered Jobs)
    
• Use parentheses so Excel performs the addition before division:
  
  • = (A + B) / (C + D)
    
• Copy down for all months.
  
• Remove decimals if you want the chart labels cleaner.
  

9. Add Target Min and Target Max inputs and make them dynamic

• Add two input cells: Target Min and Target Max (example: 60 and 75).
  
• Create two helper columns next to your monthly table:
  
  • Target Min line: reference the Target Min cell for every month
    
  • Target Max line: reference the Target Max cell for every month
    
• Copy those references down so changing the input updates the full column.
  

10. Create the line chart with upper and lower bounds

• Highlight the Month column.
  
• Hold Ctrl and also highlight:
  
  • Target Min column
    
  • Average Duration column
    
  • Target Max column
    
• Insert a Line Chart.
  
• Format the Y-axis minimum if needed (example shown: set minimum to 50).
  
• Add data labels to the Average Duration line.
  
• Format Target Min and Target Max lines as thin dashed lines.
  

11. Build a Technician by Call Type matrix

• Create a unique list of Technician Names using UNIQUE on the Tech Name column.
  
• Copy and paste values (so the list is static).
  
• Create a unique list of Call Types using UNIQUE on Call Type (Column G).
  
• Copy and paste values, then transpose the Call Types horizontally across the top row.
  

12. Calculate Average Duration by Technician and Call Type

• In the matrix body, calculate Average Duration using the same structure as the monthly version:
  
  • (Sum of minutes for Completed + Sum of minutes for Completed – Part Ordered) ÷ (Count of jobs for Completed + Count of jobs for Completed – Part Ordered)
    
• Both the sums and counts must filter by:
  
  • Technician Name (row header)
    
  • Call Type (column header)
    
  • Outcome equals “Completed” or “Completed – Part Ordered”
    
• Copy the formula across and down to fill the full matrix.
  

13. Add conditional formatting for out-of-range durations

• Add a target value cell (example shown: 65).
  
• Select the matrix.
  
• Apply Conditional Formatting:
  
  • Highlight Cell Rules
    
  • Greater Than
    
  • Reference the target cell
    
• Choose a red fill (or your warning color) so anything above the threshold stands out.