How to Calculate On Time Delivery Performance in Excel

When working with production and supply chains, it is an important task to calculate the on-time delivery performance. In this article, I will show you step-by-step guidelines to calculate on-time delivery performance in Excel.

How to Calculate On-Time Delivery Performance in Excel: Step-by-Step Procedures

Say, you have a dataset with the actual arrival time and estimated arrival time of a company to 10 individual stores. Now, you need to calculate the on-time delivery performance of the company.

Follow the steps below to calculate the on-time delivery performance in Excel.

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

First and foremost, you will need to calculate the time difference between the estimated time of arrival and the actual time of arrival.

When calculating time differences, if you subtract the greater time from the lesser time, the formula would return #### result.
To avoid this, first, click on the File tab from the Excel ribbon.

Afterward, choose the Options option from the expanded File tab.

At this time, the Excel Options window will appear.
Following, go to the Advanced tab >> put a tick mark on Use 1904 date system >> click on the OK button.

Afterward, click on cell E5 and insert the formula below.

=C5-D5

Subsequently, press the Enter key.

Afterward, place your cursor in the bottom right position of cell E5.
Subsequently, a black fill handle will appear.
Following, drag the fill handle below to copy the same formula for all the cells below.

As a result, you will get the time differences between the estimated arrival time and the actual arrival time. And, the outcome should look like this now.

📌 Step 2: Automate Comments as “On Time” or “Late”

Next, you will need to automate the comments for the time differences.

To do this, at the very beginning, click on cell F5 and insert the following formula.

=IF(E5<0,"Late","On Time")

Subsequently, hit the Enter key.

Afterward, use the fill handle feature below to copy the same formula for all the other cells below.

Thus, you will find all the comments on the time delivery performance in Excel. And, the output should look like this.

📌 Step 3: Calculate On-Time Delivery Percentage

Last but not least, you will need to calculate the on-time delivery percentage in Excel.

In order to do this, click on cell D16 first.
Following, insert the formula below and press the Enter key.

=COUNTIF(F5:F14,"On Time")/COUNTA(F5:F14)

Thus, you will get the ratio of on-time delivery with respect to all deliveries.

Afterward, to get the percentage format, click on cell D16 and press Ctrl + 1.

As a result, the Format Cells window will appear.
Following, go to the Number tab here >> choose Percentage in the Category: pane >> choose 0 in the Decimal places: option >> click on the OK button.

As a result, cell D16 will be converted into a percentage value and you would get the actual percentage of the on-time delivery performance.

Finally, you have calculated the on-time delivery performance in Excel and the final result should look like this.

Download Practice Workbook

You can download our practice workbook from here for free!

Calculate On Time Delivery Performance.xlsx

Conclusion

So, in this article, I have shown you step-by-step guidelines to calculate on-time delivery performance in Excel. I suggest you read the full article carefully and practice accordingly. I hope you find this article helpful and informative. And, you are very welcome to comment here if you have any further questions or recommendations regarding this article.

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

Get FREE Advanced Excel Exercises with Solutions!