Monitor shipments, delivery dates, and logistics performance in real-time
✓ Free to download · ✓ Excel 2016+ and Google Sheets · ✓ Custom version in 24h
Tracking shipments in a spreadsheet is worth doing only if it answers the question a customer actually asks: is it late, and by how much. This template keeps promised and actual dates side by side so that is a formula.
The actual columns, with sample rows
| A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|
| 1 | Shipment | Carrier | Promised | Actual | Days late | Status |
| 2 | SH-8841 | DPD | 2026-07-04 | 2026-07-04 | 0 | On time |
| 3 | SH-8842 | UPS | 2026-07-05 | 2026-07-08 | 3 | Late |
| 4 | SH-8843 | DPD | 2026-07-06 | — | 2 | In transit |
| 5 | SH-8844 | DHL | 2026-07-09 | 2026-07-08 | -1 | Early |
Why each one is written the way it is
=IF([@Actual]="", MAX(0,TODAY()-[@Promised]), [@Actual]-[@Promised])
Days late for delivered and in-transit shipments alike. An undelivered shipment past its promise date is late now, not once it eventually arrives.
=IFS([@Actual]="","In transit", [@[Days late]]>0,"Late", [@[Days late]]<0,"Early", TRUE,"On time")
Status derived from the dates, so it cannot contradict them.
=COUNTIFS(Ship[Carrier],[@Carrier],Ship[Status],"Late")/COUNTIFS(Ship[Carrier],[@Carrier])
Late rate per carrier — the figure to take into a rate negotiation.
=AVERAGEIFS(Ship[Days late],Ship[Carrier],[@Carrier],Ship[Status],"Late")
Average lateness when a carrier is late. A carrier that is late 5% of the time by four days is worse than one late 12% of the time by half a day.
A worked example with real numbers
A retailer was about to switch carriers on a 4% price difference. The tracking sheet showed the cheaper carrier late on 11% of shipments by an average of 3.4 days, against 4% by 0.8 days. At their volume the difference was roughly 60 additional late deliveries a month — more support contacts than the saving covered. They stayed, and used the numbers to negotiate 6% off instead.
Tab by tab
| Tab | Contents |
|---|---|
| Shipments | One row per shipment with promised and actual dates. |
| Carriers | Carrier reference with service levels and rates. |
| Exceptions | Failed deliveries, returns and claims. |
| Dashboard | Late rate and average lateness by carrier and month. |
| README | Where tracking numbers and delivery confirmations come from. |
What is included, and what to look at next
Specific to this workbook, not generic
Most carriers publish a tracking API. Fetching status on a schedule is a small Apps Script project; without it, the actual date is entered on delivery confirmation.
Yours — the date you gave the customer. Measuring against the carrier's estimate tells you about the carrier; measuring against your promise tells you about the customer's experience.
Mark it as an exception with a reason rather than leaving it in transit forever. Otherwise the days-late figure grows indefinitely and distorts the carrier averages.
"This tracking system has revolutionized our logistics operations. We've improved our on-time delivery rate by 35% and significantly reduced shipping costs."