On-Time Delivery Rate

Excel Formulas › Logistics & Trucking

All versionsCOUNTIF

On-time delivery — deliveries within the appointment window over total deliveries — is the key service metric carriers are scored on. It drives contracts, scorecards, and detention disputes.

Quick formula: on-time rate from on-time and total:
=on_time_deliveries / total_deliveries
On-time deliveries over total. Count on-time with COUNTIF on a status column. 188 of 200 is 94%.

Functions used (tap for the full reference guide):

COUNTIFSUMPRODUCT

The example

188 on time of 200.

AB
1ItemValue
2188 / 200—
3On-time→ 94%

The formula

The formula:

=on_time_deliveries / total_deliveries // on-time ÷ total

How it works

How it works:

  1. Mark each delivery on-time or late by comparing arrival to the appointment.
  2. Count on-time with COUNTIF (or compare actual ≤ appointment with SUMPRODUCT).
  3. Divide by total deliveries for the rate.
  4. Track by lane or customer to find where service slips.

Define “on time” before you measure it. Is a delivery on time if the truck arrives by the appointment, or only if it’s checked in / unloaded by then? A grace window, early-arrival rules, and the timestamp you trust all change the number. Compute on-time with SUMPRODUCT(--(actual <= appointment)) against a clearly-defined rule, and the same definition makes your detention and scorecard disputes defensible.

Try it: interactive demo

Live demo

On-time and total deliveries.

On-time rate:

Variations

Count on-time

From a status column:

=COUNTIF(status, "On time")

From timestamps

Actual ≤ appointment:

=SUMPRODUCT(--(actual <= appointment)) / total

By customer

Segment service:

=COUNTIFS(customer,c,status,"On time") / COUNTIF(customer,c)

Pitfalls & errors

Define on-time. Arrival vs unload, and any grace window — set the rule.

Consistent status. One spelling so COUNTIF matches.

Zero deliveries. No deliveries gives #DIV/0!.

Practice workbook

📊
Download the free On-Time Delivery Rate practice workbook
An on-time sheet with the count, timestamp, and by-customer variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I calculate on-time delivery rate in Excel?
On-time over total: =on_time_deliveries / total_deliveries. Count on-time with COUNTIF(status, "On time"). 188 of 200 is 94%.
How do I compute it from timestamps?
Compare actual to appointment: =SUMPRODUCT(--(actual <= appointment)) / total.
How should I define on-time?
Decide whether it's arrival or unload by the appointment, plus any grace window, and apply it consistently.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Detention pay · Time difference · Count if contains

Function references: COUNTIFSUMPRODUCT