How to Calculate On-Time Delivery Performance in Excel: A 3-Step Procedure

Let’s say your dataset contains arrival times, both actual and estimated, for ten stores, and you would like to calculate your delivery performance.

Sample Dataset to Calculate On-Time Delivery Performance in Excel


Step 1: Calculate the Difference Between Estimated Time of Arrival and Actual Time of Arrival

  • Calculate the difference between Estimated Time Arrival and Actual Time Arrival.
  • Click File.

Access the File Tab

  • Click Options.

Choose Options Option from the Expanded File Tab

  • Click Advanced  → Use 1904 date system OK.

Excel Options Window

  • Select E5 and enter the following formula.
=C5-D5
  • Press Enter.

Formula to Calculate Time Difference

  • We now know that Store 1’s actual arrival time is an hour later than its estimated arrival time.

Drag Fill Handle Below

To duplicate the formula, click and drag the Fill Handle down the targeted range.

Time Difference Between the Estimated Time Arrival and Actual Time Arrival

We now know the difference in delivery time for each of the ten stores.

Read More: How to Calculate Hours and Minutes for Payroll Excel


Step 2: Automate Comments to Account for Time Differences (Such as “On Time” or “Late”)

  • Select F5 and enter the following formula:
=IF(E5<0,"Late","On Time")
  • Press Enter.

Formula to Calculate and Comment On Time Delivery Performance in Excel

We can now see at a glance that Store 1 was late.

Drag Fill Handle Below

To duplicate the formula, click and drag the Fill Handle down the targeted range.

On Time Delivery Performance Comments in Excel

We can now see at a glance which of the stores was late and which was on time.

Read More: How to Calculate Hours Worked Minus Lunch with Excel Formula


Step 3: Calculate On-Time Delivery Percentage

  • Select D16 and enter the following formula:
=COUNTIF(F5:F14,"On Time")/COUNTA(F5:F14)
  • Press Enter.

Calculate On-Time Delivery Performance Percentage in Excel

  • To convert to a percentage, click D16, then press Ctrl+1.

Access the Format Cells Window

  • When Format Cells appears, click Number Percentage (under Category), then select 0 (under Decimal places), then click OK.

Format Cells Window

We now know that the overall on-time delivery performance is 60%.

Calculated On-Time Delivery Performance in Excel

Read More: How to Calculate Production per Hour in Excel


Download Free Practice Workbook


Related Articles 


<< Go Back to Calculate Time | Date-Time in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Tanjim Reza
Tanjim Reza

Md. Tanjim Reza Tanim, a BUET graduate in Naval Architecture & Marine Engineering, contributed over one and a half years to the ExcelDemy project. As an Excel & VBA Content Developer, he authored 100+ articles and, as Team Leader, reviewed 150+ articles. Tanim, leading research, ensures top-notch content on MS Excel features, formulas, solutions, tips, and tricks. His expertise spans Microsoft Office Suites, Automating Finance Templates, VBA, Python, and Developing Excel Applications, showcasing a multifaceted commitment to the... Read Full Bio

2 Comments
  1. Reply
    premierdigitalmark Jan 14, 2025 at 12:35 PM

    In excel how to calculate the goods delivered on time

    • Hello Premierdigitalmark,

      To calculate goods delivered on time in Excel, follow these steps:

      Prepare your data: Include columns for Estimated Delivery Date and Actual Delivery Date.
      For example, if column A has Order IDs, column B has Estimated Dates, and column C has Actual Dates, proceed to step 2.

      Calculate if each delivery is on time: In a new column (e.g., column D), enter the formula:
      =IF(C2<=B2,"On Time","Late")

      This formula checks if the actual delivery date (C2) is on or before the estimated date (B2) and labels the delivery as “On Time” or “Late.”
      Count on-time deliveries:

      Use the COUNTIF function to count the “On Time” deliveries:
      =COUNTIF(D2:D100,”On Time”)

      Replace D2:D100 with the actual range of your data.

      This will give you the total number of goods delivered on time. If you also want the percentage, divide this count by the total number of deliveries. Let me know if you need further assistance!

      Regards
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo