Let’s say your dataset contains arrival times, both actual and estimated, for ten stores, and you would like to calculate your delivery performance.
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.
- Click Options.
- Click Advanced → Use 1904 date system → OK.
- Select E5 and enter the following formula.
=C5-D5- Press Enter.
- We now know that Store 1’s actual arrival time is an hour later than its estimated arrival time.
To duplicate the formula, click and drag the Fill Handle down the targeted range.
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.
We can now see at a glance that Store 1 was late.
To duplicate the formula, click and drag the Fill Handle down the targeted range.
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.
- To convert to a percentage, click D16, then press Ctrl+1.
- When Format Cells appears, click Number → Percentage (under Category), then select 0 (under Decimal places), then click OK.
We now know that the overall on-time delivery performance is 60%.
Read More: How to Calculate Production per Hour in Excel
Download Free Practice Workbook
Related Articles
- How to Calculate Total Hours Worked in a Week in Excel
- How to Calculate Billable Hours in Excel
- Excel Formula for Overtime over 8 Hours
- How to Calculate Hours Worked and Overtime Using Excel Formula
- Excel Formula to Calculate Overtime and Double Time
- Excel Formula To Calculate Time Worked
- Man Hours Calculation in Excel
- How to Create an Injection Molding Cycle Time Calculator in Excel
<< Go Back to Calculate Time | Date-Time in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
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