Same Day Completion Rate Dashboard
(By Month + Technician)

Same Day Completion Rate

1. Create the Same Day Completion Flag Column

• Add a new column outside your original dataset called something like Same Day Completed?
  
• Build a single formula that returns Yes if the job was completed the same day, otherwise No
  
• Use two “No” conditions:
  
  • If the completion date is blank, it is No
    
  • If the completion date is greater than the date of service, it is No
    
• Everything else is Yes
  
• Keep this column outside the exported dataset so future monthly copy-pastes do not shift your column structure

2. Build the Month List for the Monthly Summary

• In a separate summary area, type January and drag down until December
  
• Add column headers:
  
  • Completed
    
  • Not Completed
    
  • Completion Rate
    
  • Minimum
    
  • Maximum

3. Count Completed and Not Completed by Month

• Use COUNTIFS to count rows where:
  
  • Same Day Completed? = Yes
    
  • Month = the month in your summary table (ex: January)
    
• Copy the formula down for all months
  
• For Not Completed, copy the same formula and change Yes to No
  
• Fast fill tip shown in the video:
  
  • Select the range and use Ctrl + D to fill down

4. Calculate Monthly Completion Rate and Add Dynamic Bands

• Completion Rate formula:
  
  • Completed ÷ (Completed + Not Completed)
    
• Format as a percent using Ctrl + Shift + 5
  
• Create two input cells for a target band (example in the video: minimum and maximum)
  
• Under Minimum and Maximum columns, reference the cell above (so the band is dynamic):
  
  • In the first month row: =cell_above
    
  • Copy down so every month points to the same band inputs
    
• This lets you change the band once and have the chart update automatically

5. Create the Monthly Chart and Add the Band Lines

• Select the Month column and Completion Rate column (the video shows selecting just these two first)
  
• Insert a chart:
  
  • Insert → Recommended Charts
    
  • Or All Charts → Combo
    
• Trick shown in the video to add the band after the chart is created:
  
  • Copy the Minimum and Maximum ranges
    
  • Click the chart
    
  • Paste to add them as new series
    
• Change chart type so the band series are Lines
  
  • Chart → Change Chart Type → set Minimum and Maximum to Line
    
• Format the band lines:
  
  • Change to gray
    
  • Adjust line width (example: 1.5)
    
  • Change to dashed line style
    
• Optional: add data labels to the completion rate line:
  
  • Click the line → right click → Add Data Labels
    
• Title the chart: Same Day Completion Rate by Month

6. Build the Technician Summary Table

• Create a unique list of technicians using UNIQUE on the Technician column (Column K)
  
  • Shortcut shown: click in the column and use Ctrl + Shift + Down Arrow to grab the full range
    
• Copy and paste values (Ctrl + C, then Ctrl + Shift + V) to remove the formula
  
• Copy the same headers used before (Completed, Not Completed, Completion Rate)
  
• If using a benchmark row:
  
  • Insert a few rows above the technician table to store a benchmark value (example: 80%)

7. Count Completed and Not Completed by Technician

• Use COUNTIFS with two criteria:
  
  • Same Day Completed? = Yes
    
  • Technician Name = the technician in the current row
    
• Copy the formula down
  
• Duplicate the formula for Not Completed by changing Yes to No
  
• Completion Rate:
  
  • Completed ÷ (Completed + Not Completed)
    
• Copy the completion rate formula down the technician list

8. Add Conditional Formatting for Under-Benchmark Techs

• Select the technician table rows you want to highlight
  
• Conditional Formatting → New Rule → Use a formula
  
• Rule logic shown:
  
  • If the technician completion rate cell is less than the benchmark cell, highlight the row
    
• Because the benchmark is in one cell, changing it instantly updates which rows get flagged

9. Create the Technician Chart and Add the Benchmark Line

• Select Technician names + Completion Rate
  
• Hold Ctrl and also select the Benchmark series (or minimum bound line series if you set it up that way)
  
• Insert → Recommended Charts → Combo
  
• Make the benchmark/bound series a Line
  
• Clean up formatting:
  
  • Gray, 1.5 width, dashed
    
• Title the chart: Same Day Completion Rate by Technician

10. Format the Benchmark Cell to Show Text Without Breaking the Number

• Keep the benchmark as a numeric percent so it still works in formulas
  
• To display the word “Benchmark” before the percent:
  
  • Press Ctrl + 1
    
  • Number → Custom
    
  • Add a custom format that prefixes text while keeping it numeric (example shown: Benchmark: 80% style)
    
• This keeps it usable in calculations while making it clearer for the team