Blog / Technical Guides

Purchase Order Tracker That Chases Late Suppliers

By Keyona Meeks, Founder, ReRev Labs · Oct 31, 2026 · 9 min read

The short answer: One row per purchase order, with the promised date and the quantity received so far. Four formulas do the rest: a status (Open, Late, Partial or Received), a count of days late, a Chase flag that appears when an order is late enough and has not been chased recently, and a drafted message you can paste into an email. A person sends the message and types the date into the Last chased column. I tested the formulas in LibreOffice with made-up orders, not in Google Sheets. The sheet tracks. It does not tell you what your supplier agreed to.

By the end of this post, you'll be able to:

• Lay out a purchase order tracker with the columns that matter.
• Get a status and a days-late count from formulas.
• Flag which suppliers to chase today, and not chase the same one twice in a row.
• Draft the chase message from the row.

This works for a product brand ordering stock and for a contractor ordering materials. The pattern is the same: an order was promised for a date, the date passed, and nobody noticed until the stock or the materials were needed.

I am not an attorney. What a late delivery means for your contract, your payment terms or your schedule is a question for your attorney and your agreement. This sheet only tells you which orders are late and when you last asked.

What columns does the tracker need?

Make a tab named PO with these headings in row 1:

In N1 type Chase after days late and in N2 type 2. In O1 type Days between chases and in O2 type 3. Those are settings, and they are my starting numbers. Pick your own.

Keep a record of the promised date in writing, such as the supplier's confirmation email. The sheet is a copy of that, not the proof.

Which formulas do the work?

In row 2:

I2: =IF(A2="","",IF(N(G2)>=F2,"Received",IF(TODAY()>E2,IF(N(G2)>0,"Partial, late","Late"),IF(N(G2)>0,"Partial","Open"))))
J2: =IF(OR(A2="",I2="Received"),"",MAX(0,TODAY()-E2))
K2: =IF(J2="","",IF(J2<$N$2,"",IF(OR(H2="",TODAY()-H2>=$O$2),"Chase","Wait")))
L2: =IF(K2<>"Chase","","Hi "&B2&", PO "&A2&" for "&C2&" was promised for "&TEXT(E2,"mmm d")&". We show "&N(G2)&" of "&F2&" received. Can you confirm a ship date?")

Fill them down. What each does:

• Status. Received when the quantity received has caught up with the quantity ordered. Late when today is past the promised date and nothing has arrived. Partial and Partial, late cover a delivery that is short. Open is an order not yet due.
• Days late. Today minus the promised date, never below zero. It is blank for received orders.
• Chase? Blank until the order is at least as late as your first setting. Then it says Chase if you have never chased, or if your last chase was at least the second setting ago. Otherwise it says Wait. That second rule stops you from sending the same supplier three messages in one day.
• Message. A draft that names the supplier, the order, the item, the promised date and how much has arrived. It appears only when the flag says Chase.

TODAY() changes every day, so the status and the days late update on their own whenever the sheet is opened.

What did the test show?

I tested the formulas in LibreOffice with eight made-up orders dated around the day I ran it. A fully received order showed Received with no days late. An order due five days ago with nothing received showed Late, 5 days, and Chase. An order one day late showed Late and no flag, because it had not reached the first setting. A partial order six days late that I had chased yesterday showed Partial, late, 6 days, and Wait. A late order chased five days ago showed Chase again. An order not yet due showed Open. An order due today showed Open with zero days late. A partial order not yet due showed Partial. The drafted message for one order read, "Hi Bolt Co, PO 1002 for Bolts was promised for Oct 26. We show 0 of 200 received. Can you confirm a ship date?" All eight statuses and the flags matched what I expected.

I did not test the formulas in Google Sheets. TEXT(E2,"mmm d") is the one I would check first, since date format codes can differ. Type a test row and look at the message.

Who sends the chase?

A person. The sheet marks the row and drafts the words. You copy the message, send it, and type today's date into Last chased. That last step is what keeps the flag honest. If nobody types the date, the sheet will ask you to chase again the next day.

I did not build an automatic sender on purpose. A message that goes to a supplier cannot be taken back, and a supplier who already shipped will not enjoy being chased. Run the sheet by hand for a few weeks and see how often the flag is wrong before you consider automating the send.

Who gets the message?

You decide. The draft is addressed to the supplier name in column B. If you add a contact email column, use it. Some teams copy a manager or a buyer. Others do not want that. Ask the people involved before you set up any copy, and do not assume.

What should the sheet not decide?

• Whether a late order matters. A week late on paint may be nothing. A week late on a structural part may stop a job.
• What the supplier owes you. That depends on your agreement. Check with an attorney.
• Who else to call. An alternate source or an expedite is a person's decision.
• Whether the received quantity is right. Someone has to count the delivery.

Where does an LLM fit in this?

An LLM can help with the messy part: reading a supplier's confirmation email and suggesting the promised date and quantity to enter, or softening or sharpening the chase message for a particular relationship. A person should check any date it extracts against the email before it goes in the sheet. The status, the day count and the chase timing are plain rules, and they do the same thing every time. My stance: AI writes the code, plain automation runs it, and an LLM shows up only where the input is messy and a person checks the result. For other jobs worth automating next, see what to automate first, and for the thinking behind follow-ups like this one, see the follow-up engine.

How do you test the sheet?

1. Enter five made-up orders: one received, one late and never chased, one slightly late, one late and chased yesterday, one not yet due.
2. Check each status and days-late number against a calendar.
3. Confirm the right rows say Chase and Wait.
4. Type today's date into Last chased on a Chase row. The flag should change to Wait.
5. Change the two settings and watch the flags move.
6. Read a drafted message for typos and for the date format.
7. Delete the test rows and keep the formulas.

Where this has limits

• I did not use this on live orders. The formulas were tested in LibreOffice with made-up data, not in Google Sheets.
• The settings are my starting numbers. Two days and three days are examples.
• The date format in the message is untested in Google Sheets.
• The sheet knows only what you type. A wrong promised date or a wrong received count gives a wrong status.
• It sends nothing. A person sends every chase.
• I am not an attorney. Check with an attorney on what a late delivery means under your agreement.
• No ReRev prices and no promises about results.

If you want help turning this into a system that watches your orders and prepares the follow-ups, see our AI operations service or tell us which orders go late. Do not send purchase orders, supplier contracts, passwords or login details.

Frequently asked questions

What should a purchase order tracker include?

An order number, supplier, item, order date, promised date, quantity ordered, quantity received and a place to record when you last followed up.

How do I know which suppliers to chase?

A formula can flag orders that are late by at least the number of days you set and have not been chased recently.

Can the sheet email suppliers for me?

This one does not. It drafts the message and a person sends it.

How do I handle partial deliveries?

Type the running total in Qty received. The status shows Partial, or Partial, late if the date has passed.

Does it work for construction materials?

For tracking, yes. What a late delivery means for your contract is a question for your attorney.

Will it work in Excel?

The functions are standard, but I tested it in LibreOffice, not Excel or Google Sheets.

Read next: The reorder point formula decides when a purchase order should exist in the first place, and the approval workflow adds sign-off routing in front of the orders this tracker chases.