Logistics & shipping

Ocean Shipment Tracker

One row per shipment, every milestone dated, and the days sitting in each stage — so a delay is visible while it is still fixable

Download the template — free

✓ No account, no email · ✓ Excel 2016+ and Google Sheets · ✓ Free for commercial use

Most shipment trackers record an ETA and an actual arrival, which tells you a container was late after it was late. A shipment is not one date, it is seven: booked, documents to the agent, cleared, gated in, sailed, arrived, delivered. The gap that hurt you is always between two of them, and you cannot see it unless each is recorded. This sheet derives the duration of every leg and the days a shipment has been sitting in its current stage, which is the number that tells you who to call this morning.

What the worksheet looks like

The actual columns, with sample rows

Shipments sheet: milestones typed, durations and current stage derived.
A	B	C	D	E	F	G	H	I	J	K	L
1	Ref	B/L	Carrier	POL	POD	Docs to agent	Cleared	Sailed	Arrived	Stage	Days in stage
2	SH-1042	MSCU7741233	MSC	Tema	Rotterdam	2026-02-03	2026-02-09	2026-02-11	2026-03-04	Delivered	0
3	SH-1043	MAEU8820114	Maersk	Tema	Antwerp	2026-02-14	2026-02-27			Awaiting loading	16
4	SH-1044	CMAU5512900	CMA CGM	Lagos	Le Havre	2026-03-01				In clearance	11

The formulas that do the work

Every one of these is in the file you download

=IF(OR($F5="",$G5=""),"",$G5-$F5) Days in clearance: documents handed to the agent until cleared. The leg most often blamed on the carrier and most often not theirs.
=IF(OR($H5="",$I5=""),"",$I5-$H5) Sea transit, sailed to arrived. Compare it to the carrier's quoted transit rather than to your hopes.
=IF($J5<>"","Delivered",IF($I5<>"","At destination",IF($H5<>"","In transit",IF($G5<>"","Awaiting loading",IF($F5<>"","In clearance","Booked"))))) The current stage, derived from which milestone is the last one filled. Nobody has to remember to update a status column, so it cannot go stale.
=IF($A5="","",IF($J5<>"",0,$P$2-MAX($F5:$J5))) Days sitting in the current stage, measured from the most recent milestone. This is the column to sort by: a shipment that has not moved in 16 days is the one to chase, whatever its ETA says.
=IFERROR(AVERAGEIFS(Shipments!$K$5:$K$204,Shipments!$C$5:$C$204,$A5),"") Average clearance days per carrier, so a pattern separates from a bad week.

Sixteen days that no ETA would have shown

A worked example with real numbers

SH-1043 leaves Tema for Antwerp. Documents reach the clearing agent on 14 February, clearance comes through on 27 February — thirteen days against a four-day average — and then nothing. The ETA in the carrier's portal still reads 12 March and still looks fine, because the carrier has not received the box. What the sheet shows is Stage: Awaiting loading, Days in stage: 16. The container is sitting at the port because a booking was never confirmed after clearance ran long. Sorted by days in stage, it is the top row on a Monday morning; sorted by ETA, it is invisible until it is three weeks late.

What is in the workbook

Every tab, and what it holds

Every tab in the workbook and what it is for.
Tab	Contents
README	What to fill in and what is calculated, with the colour key.
Shipments	The register: one row per shipment, 200 rows, milestones typed and every duration derived.
By carrier	Shipment count, average clearance, average transit and on-time share per carrier.
Open	Everything not yet delivered, ranked by days in the current stage.

Features and related templates

What is included, and what to look at next

What it does

Seven dated milestones, from booking to delivery
Duration of every leg derived, not typed
Current stage deduced from the last milestone filled
Days sitting in the current stage — the column to sort by
Per-carrier averages for clearance and transit
Excel 2016+ and Google Sheets

Related templates

Need it built around your own data?

Your own lanes, carriers and milestone names
Connected to your forwarder's exports
Delivered within 24 hours

Get your Excel job done

Questions about this template

The ones that come up most

We do not use every milestone. Does it still work?

Yes. Leave a column blank and the stage formula simply falls through to the last one you did fill. The only requirement is that the milestones you use are in chronological order across the row.

Can it track air and road as well as sea?

Yes — the milestones are generic enough (documents, cleared, departed, arrived, delivered) that only the column headings need renaming. The per-leg durations and the stage logic are unchanged.

How do I handle a shipment that goes backwards, like a customs query?

Record the date the query was resolved in the Cleared column rather than the first attempt. The days-in-stage figure then keeps counting through the query, which is the behaviour you want: a shipment stuck in a query is stuck.