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!
