To create batch numbers in Excel, build each one from parts you already have: a short product code, the date the batch
was made, and a letter for which batch of that product it was that day. One formula joins them:
=B2&"-"&TEXT(A2,"yymmdd")&"-"&C2 turns product TC, made on October 12, 2026, first batch of the day, into TC-261012-A.
A COUNTIFS formula works out the letter for you, a variation gives a Julian date code if you prefer, and a VLOOKUP adds
the best-by date from each product's shelf life. Below is each formula, how they fit together, and a free template that
does all of it.
The short version
- A batch number needs to be unique and readable: product, date, batch that day.
- Lot code:
=B2&"-"&TEXT(A2,"yymmdd")&"-"&C2. - Batch letter: count the same product on the same date so far, and turn the count into a letter.
- Julian code: year plus day of the year,
=TEXT(A2,"yy")&TEXT(A2-DATE(YEAR(A2),1,0),"000"). - Best by: the date plus the product's shelf life, looked up with VLOOKUP.
- Never retype a batch number by hand; let the formula build it.
- TaroStack gives every batch its lot code and date automatically, and prints the label. More below.
The sheet
Two tabs. Products holds a short code for each product and how many days it keeps. Batches gets one row per batch: you type the date made and the product code, and formulas fill in the rest.
| Date made | Product | Batch that day | Lot code | Julian lot code | Best by |
|---|---|---|---|---|---|
| Oct 12, 2026 | TC | A | TC-261012-A | TC26285A | Jan 10, 2027 |
| Oct 12, 2026 | TC | B | TC-261012-B | TC26285B | Jan 10, 2027 |
| Oct 12, 2026 | TB | A | TB-261012-A | TB26285A | Oct 15, 2026 |
| Oct 13, 2026 | GJ | A | GJ-261013-A | GJ26286A | Oct 13, 2027 |
These are Windward Roots' batches (the made-up taro business I use in these examples): two fry runs of taro chips (TC) on the 12th, a bake of taro sweet bread (TB), and a guava jam (GJ) the next day.
The formulas
The batch letter (C2). Count how many rows so far have the same date and the same product, then turn that count into a letter:
=RIGHT(LEFT("ABCDEFGHIJKLMNOPQRSTUVWXYZ", COUNTIFS($A$2:A2, A2, $B$2:B2, B2)), 1)
The ranges start at row 2 and grow as you fill down, so the first TC batch on October 12 counts 1 (A), the second counts 2 (B), and a different product or date starts again at A.
The lot code (D2).
=B2 & "-" & TEXT(A2, "yymmdd") & "-" & C2
Year, month and day in that order sort correctly as text, which makes a list of lot codes easy to scan.
The Julian lot code (E2). Some makers prefer the day of the year, which fits more in fewer characters:
=B2 & TEXT(A2, "yy") & TEXT(A2 - DATE(YEAR(A2), 1, 0), "000") & C2
October 12, 2026 is day 285, so it reads TC26285A. More on reading and printing Julian codes is in how to create lot numbers.
The best-by date (F2).
=A2 + VLOOKUP(B2, Products!$A$2:$C$100, 3, FALSE)
Chips keep 90 days, so October 12 becomes January 10. Format the cell as a date, and print it in a form nobody misreads, covered in best by date format.
Get the template
Download the batch numbers template. It has the Products tab and the Batches tab with every formula above, filled with the example rows: type a date and a product code and the batch letter, lot code, Julian code and best-by date fill themselves in. It's an ordinary Excel file with no macros, and it works in Google Sheets, LibreOffice and Numbers.
Rules that keep batch numbers useful
| Rule | Why |
|---|---|
| One format, forever | So last year's codes still make sense |
| Never reuse a code | Every code points to exactly one batch |
| Short product codes | Fit on small labels and stamps |
| Date made, not date packed, unless you choose otherwise and stick to it | So the code means the same thing every time |
| Record the ingredient lots with the batch | So the code leads back to what went in |
The last row is the one a spreadsheet can't do on its own: a batch number is only useful in a recall if it also tells you which supplier lots went into that batch. How that chain works is in what is lot tracking.
Where this stops working
The formulas make good codes; the trouble is everything around them. The code in the sheet has to be copied onto a label or a stamp, where one wrong digit breaks the link. The ingredient lots that went into each batch live somewhere else, if anywhere. And the sheet doesn't know where each batch went, so a recall still means searching through orders.
How TaroStack does it
TaroStack numbers every batch for you and keeps the whole chain behind the number.
Every batch gets its lot and date. Record a batch and its output gets a lot code and dates, with the best-by date filled in from the item's shelf life, counting from the day the batch was finished. You can use your own lot codes or the supplier's for what you buy. The ingredients come off stock from the lots you used, noted in the batch record automatically.
The label prints from the lot. Lot labels print with the lot code, the item and the date, plus a GS1-128 barcode with the product code, use-by date and lot when the item has a 14-digit GTIN, on a sheet for an office printer or as a ZPL file for a Zebra. No retyping, so no wrong digit.
And the number leads everywhere. Oldest dates go first everywhere stock is picked, each delivery records which lot went to which customer, and one report walks any lot back to its ingredient lots and forward to every customer who got it.
| The job | Spreadsheet formulas | In TaroStack |
|---|---|---|
| Build the code | COUNTIFS, TEXT and VLOOKUP | Automatic, with the batch |
| Get it on the package | Copy to a label by hand | Lot label printed from the lot |
| What went into the batch | Somewhere else | In the batch record |
| Where it went | Search the orders | One report, lot to customers |
Lots, lot labels and recalls are on every plan, from $49 a month; recording batches with their ingredient lots is on Standard at $99. The first 30 days are free, with no credit card. If you'd like a hand setting up, ask, and we'll do it with you.