Which suppliers deliver late, and by how much?
By the Analistable team · Updated · 3 min read
Join purchase orders (with the promised date) to delivery records (with the delivered date) on PO number, calculate days late, and group by supplier. In the example, Fleet Ltd delivered both orders late (3.5 days on average), PackCo one of two, and Print Hub was on time.
Part of our guide: How to answer questions across multiple spreadsheets
The data
| PO | Supplier | Promised | Delivered |
|---|---|---|---|
| PO-1 | PackCo | 2026-09-05 | 2026-09-05 |
| PO-2 | PackCo | 2026-09-12 | 2026-09-16 |
| PO-3 | Fleet Ltd | 2026-09-08 | 2026-09-11 |
| PO-4 | Fleet Ltd | 2026-09-15 | 2026-09-19 |
| PO-5 | Print Hub | 2026-09-10 | 2026-09-10 |
In a spreadsheet
Delivered =XLOOKUP([@PO], Deliveries[PO], Deliveries[Date], "")
Days late =IF([@Delivered] = "", "", MAX(0, [@Delivered] - [@Promised]))
On time % =COUNTIFS(Orders[Supplier], A2, Orders[Days late], 0) / COUNTIFS(Orders[Supplier], A2)In SQL
SELECT o.supplier,
COUNT(*) AS orders,
SUM(julianday(d.delivered) > julianday(o.promised)) AS late,
ROUND(AVG(MAX(julianday(d.delivered) - julianday(o.promised), 0)), 1) AS avg_days_late
FROM orders o
JOIN deliveries d USING (order_id)
GROUP BY o.supplier;| supplier | orders | late | avg_days_late |
|---|---|---|---|
| Fleet Ltd | 2 | 2 | 3.5 |
| PackCo | 2 | 1 | 2 |
| Print Hub | 1 | 0 | 0 |
Watch out for
- Orders not yet delivered drop out of an inner join; list them separately with a left join and compare the promised date with today.
- Part deliveries: use the date of the final delivery, or measure each line separately.
- Working days vs calendar days:
NETWORKDAYS(promised, delivered) - 1gives business days late.
Ask it in Analistable: “Which suppliers delivered late most often this quarter, and by how many days on average?” Customer-facing deliveries work the same way with order and shipment exports.
Open orders that are already late
=FILTER(Orders[[PO]:[Promised]], (COUNTIF(Deliveries[PO], Orders[PO]) = 0) * (Orders[Promised] < TODAY()), "None")These POs haven't been delivered and their promised date has passed — chase them before they become a stock-out. In SQL it's a left join from orders to deliveries where the delivery is NULL and the promised date is before today.
A supplier scorecard
Add on-time percentage, average days late and number of orders per supplier for each quarter, side by side. Trends matter more than one quarter: a supplier slipping from 95% to 80% on time needs a conversation even if they're still above the others.
Business days late, and the weekend trap
| PO | Promised | Delivered | Calendar days late | NETWORKDAYS − 1 |
|---|---|---|---|---|
| PO-1 | Sat 5 Sep | Sat 5 Sep | 0 | -1 |
| PO-2 | Sat 12 Sep | Wed 16 Sep | 4 | 2 |
| PO-3 | Tue 8 Sep | Fri 11 Sep | 3 | 3 |
| PO-4 | Tue 15 Sep | Sat 19 Sep | 4 | 3 |
| PO-5 | Thu 10 Sep | Thu 10 Sep | 0 | 0 |
When the promised date falls on a weekend, NETWORKDAYS(promised, delivered) − 1 can return −1 for an on-time delivery, because the weekend start date isn't counted. Wrap it: =MAX(0, NETWORKDAYS([@Promised], [@Delivered]) - 1). On business days, PackCo's PO-2 was 2 days late rather than 4.
Overall figures
- On-time rate across all suppliers: 2 of 5 POs = 40% (PO-1 and PO-5).
- Average days late counts on-time orders as 0. Among late orders only, PackCo's average is 4 days, not 2 — report both if suppliers dispute the figure.
- In Excel 2019, replace XLOOKUP with
=IFERROR(INDEX(Deliveries!B:B, MATCH(A2, Deliveries!A:A, 0)), ""); COUNTIFS and NETWORKDAYS work in every version, on Windows and Mac.
Troubleshooting
| Symptom | Cause | Fix |
|---|---|---|
| Huge negative days late | Delivery date missing, treated as 0 | Filter undelivered POs separately |
| PO matched to the wrong delivery | PO numbers reused or typed with spaces | Match on PO and supplier, trimmed |
| Supplier looks worse than reality | Promised date changed but not updated | Use the latest agreed date and log changes |
For the purchasing side of the same data, see three-way matching POs, receipts and invoices.
Frequently asked questions
- How do I measure on-time delivery in Excel?
- Look up each order's delivery date, calculate days late against the promised date, and count orders with zero days late per supplier.
- How do I count business days late?
- Use NETWORKDAYS(promised, delivered) − 1, optionally with a holiday list.
- What about orders not delivered yet?
- Use a left join and compare their promised date with today to list overdue open orders.
- Why does NETWORKDAYS return −1?
- The promised date falls on a weekend, so it isn't counted. Wrap the formula in MAX(0, ...).
- Should average days late include on-time orders?
- Report both: the average across all orders, and the average across late orders only.
- Can I score suppliers each quarter?
- Yes. Add a quarter column and group by supplier and quarter to see trends.