How to Calculate On Time Delivery Performance in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.


Download Practice Workbook

You can download our practice workbook from here for free!


Steps to Calculate On Time Delivery Performance in Excel

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.

Sample Dataset to Calculate On-Time Delivery Performance in Excel

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


📌 Step 1: Calculate the Difference Between Estimated Time of Arrival and 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.

Access the File Tab

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

Choose 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.

Excel Options Window

  • Afterward, click on cell E5 and insert the formula below.
=C5-D5
  • Subsequently, press the Enter key.

Formula to Calculate Time Difference

  • 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.

Drag Fill Handle 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.

Time Difference Between the Estimated Time Arrival and Actual Time Arrival

Read More: How to Calculate Time in Excel (16 Possible Ways)


📌 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.

Formula to Calculate and Comment On Time Delivery Performance in Excel

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

Drag Fill Handle Below

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

On Time Delivery Performance Comments in Excel

Read More: How to Calculate the Duration of Time in Excel (7 Methods)


Similar Readings


📌 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.

Calculate On-Time Delivery Performance Percentage in Excel

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

Access the Format Cells Window

  • 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.

Format Cells Window

  • 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.

Calculated On-Time Delivery Performance in Excel

Read More: How to Calculate Percentage of Time in Excel (4 Suitable Examples)


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.

Visit ExcelDemy to learn about many more Excel problem solutions, tips, and tricks. Thank you!


Related Articles 

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo