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.


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.

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

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 Hours and Minutes for Payroll Excel


📌 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 Hours Worked Minus Lunch with Excel Formula


📌 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 Production per Hour in Excel


Download Practice Workbook

You can download our practice workbook from here for free!


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.


Related Articles 


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo