Tanjim Reza
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.
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.
- 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.
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.
- 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.
Read More: How to Calculate the Duration of Time in Excel (7 Methods)
Similar Readings
- How to Calculate Turnaround Time in Excel Excluding Weekends (3 Ways)
- Excel Formula to Calculate Overtime and Double Time (3 Ways)
- Excel Timesheet Formula with Lunch Break (3 Examples)
- [Fixed!] SUM Not Working with Time Values in Excel (5 Solutions)
- 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.
- 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.
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
- How to Subtract Minutes from Time in Excel (7 Methods)
- How to Calculate Hours and Minutes in Excel (7 Handy Ways)
- Excel Calculate Hours between Two Times after Midnight (3 Methods)
- Calculate Hours Between Two Dates and Times in Excel Excluding Weekends
- How to Calculate Travel Time Between Two Cities in Excel
- How to Calculate Production per Hour in Excel (4 Ways)
