Written with AI assistance. I edited it. The formulas below were checked in LibreOffice Calc on sample rows (not in Excel or Google Sheets).
If you sell on more than one channel (say Etsy, Shopify, eBay and Instagram DMs), each one has its own order screen and none of them tells you the two things that matter at 9 a.m.: what has to ship today, and which orders are already late.
Here's a single order sheet that answers both, plus what each order actually made after postage and fees. It uses WORKDAY, NETWORKDAYS, COUNTIF and SUMIFS, with no macros.
1. Settings and the order list
At the top of the sheet:
- B1: Handling days, the number of working days you promise to ship within (2 here).
-
B2: As of, today's date.
=TODAY()works; I used a fixed date so the results below don't change. - U2:U10: Holidays, days you don't ship. Blank cells are ignored.
Orders from row 5: A Order date, B Order no, C Channel, D Price, E Shipping charged, F Item cost, G Postage, H Fees (platform and payment fees), I Shipped date, J Delivered date. Give column C a dropdown (Data validation → List) so "etsy" and "Etsy " don't become two channels.
2. The ship-by date
K5:
=IF(A5="","",WORKDAY(A5,$B$1,$U$2:$U$10))
WORKDAY counts forward in working days, skipping weekends and your holidays. An order on Friday with 2 handling days must ship by Tuesday, not Sunday. Format K as a date.
3. Status
L5:
=IF(A5="","",IF(J5<>"","Delivered",IF(I5<>"","In transit",IF($B$2>K5,"LATE - ship now","To ship"))))
The order of the tests matters. A delivered date wins over a shipped date, and only orders with neither can be "To ship" or "LATE". You fill in two dates per order; the status works itself out.
4. Days late
M5, in working days:
=IF(A5="","",IF(I5<>"",MAX(0,NETWORKDAYS(K5,I5,$U$2:$U$10)-1),IF($B$2>K5,NETWORKDAYS(K5,$B$2,$U$2:$U$10)-1,0)))
For shipped orders it measures how late the shipping was (0 if on time). For unshipped orders it measures how late they are today. NETWORKDAYS counts both end dates, so the -1 makes "shipped on the ship-by date" read 0. Format M as a Number.
This column is worth keeping even after orders ship: a run of 1s and 2s on one channel is an early warning that your handling time is too optimistic.
5. Profit and margin per order
N5, profit:
=IF(A5="","",D5+E5-F5-G5-H5)
O5, margin (format as %):
=IF(OR(N5="",D5+E5=0),"",N5/(D5+E5))
Including shipping charged and postage paid is the point. An order where you charge 4 for shipping and pay 6 to post it quietly loses 2, and a per-order profit column shows it.
6. The summary
Put the four statuses in Q5:Q8 and count them in R5:
=COUNTIF($L$5:$L$504,Q5)
Then the totals and a check:
| Cell | Formula |
|---|---|
| R9: Orders counted by status | =SUM(R5:R8) |
| R10: All orders | =COUNTA($B$5:$B$504) |
| R11: Check | =IF(R9=R10,"OK","CHECK") |
| R12: Total profit | =SUM(N5:N504) |
| R13: Overall margin | =IF(SUM(D5:D504)+SUM(E5:E504)=0,"",R12/(SUM(D5:D504)+SUM(E5:E504))) |
| R14: Revenue, last 30 days | =SUMIFS(D5:D504,A5:A504,">"&($B$2-30))+SUMIFS(E5:E504,A5:A504,">"&($B$2-30)) |
| R15: Delivered but never marked shipped | =COUNTIFS(I5:I504,"",J5:J504,"<>") |
And a channel table (channels in Q18:Q21):
=COUNTIF($C$5:$C$504,Q18)
=SUMIFS($N$5:$N$504,$C$5:$C$504,Q18)
R11 catches an order with no order date (it has no status, so the counts fall short). R15 catches a delivered date typed into a row with no shipped date, which would hide a late shipment.
Six made-up orders, as of Wednesday 28 Oct 2026, with 2 handling days:
| Order | Ordered | Channel | Ship-by | Status | Days late | Profit | Margin |
|---|---|---|---|---|---|---|---|
| 1001 | Mon 19 Oct | Etsy | Wed 21 Oct | Delivered | 0 | 23.80 | 53% |
| 1002 | Wed 21 Oct | Shopify | Fri 23 Oct | In transit | 1 | 11.40 | 46% |
| 1003 | Fri 23 Oct | eBay | Tue 27 Oct | LATE - ship now | 1 | 29.50 | 43% |
| 1004 | Mon 26 Oct | Etsy | Wed 28 Oct | To ship | 0 | 11.40 | 52% |
| 1005 | Thu 22 Oct | Mon 26 Oct | In transit | 0 | 21.00 | 60% | |
| 1006 | Sat 24 Oct | Shopify | Tue 27 Oct | LATE - ship now | 1 | 25.60 | 47% |
Statuses: 1 to ship, 2 late, 2 in transit, 1 delivered, so 6 of 6 counted and the check said OK. Profit 122.70 on 250.00 of revenue (49%). By channel: Etsy 2 orders, 35.20; Shopify 2, 37.00; eBay 1, 29.50; Instagram 1, 21.00.
Two things stand out. Order 1006 came in on a Saturday and was already late by Wednesday, because the two handling days started on Monday. And order 1002 shipped a working day late (Monday instead of Friday) even though it now shows as "In transit", which is exactly what the days-late column is for.
7. Test it before you trust it
Add rows you know the answer to, then confirm:
- an order placed on a Friday (ship-by should be the following Tuesday with 2 handling days)
- an order whose ship-by date falls on one of your holidays (it should move past it)
- an order shipped exactly on its ship-by date (days late should be 0)
- a delivered date with no shipped date (R15 should say 1)
Then delete them.
This is an order-tracking method, not accounting or tax advice. Check your profit figures against your platform payouts.
About me, and a disclosure: I'm Ashish, an Excel and Google Sheets builder with 18+ years of work experience, and I write these tutorials and build my workbooks with AI assistance under my direction.
Top comments (0)