An expiry sheet that checks itself

Reading time: about 10 minutes

The situation. Expiry control is a notebook, a sheet taped up in the stockroom or — most often — the memory of whoever has been there longest. And every so often something goes in the bin.

This is the first case of the block and deliberately the easiest of the four: automating starts here, not with an agent. A spreadsheet that turns red for anything expiring within 90 days never crashes, costs nothing, sends nothing anywhere and solves the whole problem. Before automating something complicated, it helps to have automated something that works.

What you need to hand: Excel or Google Sheets, and some time to enter what you already have written down. If you have nothing written down, start with one shelf: a sheet with twenty products exists, and a sheet with the whole stockroom never gets finished.

Step by step

Decide the columns before typing any

Five, and no more: product, batch, expires, units, where it is. The last one is the one almost nobody adds and the one that makes the sheet useful: knowing something expires is no good if it takes twenty minutes to find.

Dates are always written the same way — 31/01/2027 — down the whole column. A date written as text ("Jan 27") cannot be calculated by any formula, and it is the number one reason these sheets do not work.

Check the sheet understood them as dates: if they align to the right on their own, they are dates. If they stay left, they are text and the column format needs fixing before you go on.

Ask for the formulas and for an explanation

The important part of the prompt is not the formulas: it is "explain what each one does". A sheet with formulas you do not understand breaks the day you add a column and you will not know how to fix it. This case is not about having a sheet: it is about having a sheet that is yours.

I have a spreadsheet in Google Sheets to control expiry dates in a
pharmacy. Columns:

A: Product
B: Batch
C: Expires (date, dd/mm/yyyy)
D: Units
E: Where it is

Give me, to paste into row 2 and drag down:

1. In F: the DAYS remaining until expiry.
2. In G: a text status: "EXPIRED", "URGENT" (under 30 days), "CHECK"
   (under 90) or "OK".
3. The conditional formatting so the whole row colours: red if expired,
   orange if urgent, yellow if it needs checking.
4. A cell at the top telling me how many products are in each status.

FOR EACH FORMULA, explain in two lines WHAT IT DOES, as if I knew
nothing about spreadsheets. I want to be able to change the 30 and the
90 myself without breaking it.

Tell me as well what happens if a date cell is empty, because it will
happen.

Test it with three dates whose answer you know

This is the check of this case and it is the same idea as testing an agent before letting it loose: you feed it cases whose answer you know and see if it gets them right.

A date from last year: must come out EXPIRED. One two weeks away: URGENT. One two years away: OK. And a fourth, the one that matters: leave a date cell empty and see what it does. If it paints the row red, you have a sheet that will shout at you for every half-filled line and you will stop looking at it within a week.

If something is wrong, do not rewrite the formula by eye: say "with an empty cell I get EXPIRED and it should stay blank". It fixes it and explains why it happened.
Add the automatic alert, which is what makes it automatic

A sheet you have to remember to open is not automated: it depends on you. The step that changes that is the sheet alerting you.

In Google Sheets this is a small script that runs on its own once a week and emails you what is red. You do not need to know how to write it: you ask.

And there is one instruction that cannot be left out of that prompt: the script only READS the sheet and never writes to it. It is the same rule as the read-only agent two cases further on, and this is where you practise it for the first time.
```
Write me a Google Apps Script for this sheet.

WHAT IT MUST DO:
- Run Mondays at 8 in the morning.
- Read the sheet and keep the rows whose status (column G) is "EXPIRED"
  or "URGENT".
- Email me at my address with that list: product, batch, days
  remaining and where it is.
- If there are none, send NO email at all.

RULES:
- The script ONLY READS the sheet. It does not write, delete or modify
  ANYTHING.
- It emails nobody but me.
- If something fails, say so in the email rather than failing silently.

Explain step by step where this is pasted and how to schedule it to run
on its own, as if I had never done it. And tell me what permissions
Google will ask for and why.
```
Note the "if there are none, send no email" instruction. It is the same lesson as this site’s own BOE cron: a weekly email saying "0 products" trains you to ignore the alert, and then the day it arrives with fifteen nobody looks at it.
What this sheet does NOT replace

It replaces the notebook and one person’s memory. It does not replace your pharmacy software’s expiry control if you have one, nor physically checking the shelf, nor any records you are required to keep.

It is a support, and as such it has a specific weakness worth remembering: it only knows what somebody entered. A product nobody wrote down does not expire in the sheet, and a sheet that has been green for two months may mean nothing is expiring or that nobody is entering anything. Those are opposites and they look identical.

Which is why it is worth recording somewhere the date something was last added. It is one more column and it tells you which of the two you are looking at.

A full example, and what is wrong with it

The step 2 sheet with step 3’s four test dates in: one from last year, one two weeks away, one two years away, and an empty cell.

What it replies

Row 2 — expires 15/03/2026 → F2 = -185 → G2 = EXPIRED ✔

Row 3 — expires 30/09/2026 → F3 = 14 → G3 = URGENT ✔

Row 4 — expires 01/10/2028 → F4 = 745 → G4 = OK ✔

Row 5 — C5 EMPTY → F5 = -46281 → G5 = EXPIRED ✘

And the cell above counts: "EXPIRED: 2 · URGENT: 1 · CHECK: 0 · OK: 1".

And now, what is wrong with that reply:

Three of four are right, and the fourth is the one that matters. The empty cell gives -46281 because Excel reads it as day zero of its calendar, 1 January 1900. The number is absurd and the label is believable: the row turns red and is indistinguishable from a genuinely expired one. On a twenty-line sheet you notice; on a two-hundred-line one, you do not.
The counter above says 2 EXPIRED and that propagates. That is the failure leaving the row and entering the summary. Add the step 4 automatic alert and Monday’s email will tell you there are two expired products and one of them does not exist. Two Mondays like that and you stop opening the email — which is exactly how an automation dies.
The negative days are correct and useful. -185 is not an error: it is how many days it has been expired, and sorting by that column puts what has been sitting longest at the top. It is a side effect you did not ask for and worth knowing about — not everything that looks odd is wrong.
And it gives you the fix the moment you mention it. Write "with an empty date cell I get EXPIRED and it should stay blank" and it returns the formula with an emptiness check in front, apologising. The good formula existed all along; the only thing missing was testing the case — which is this whole lesson.

Four test rows, two minutes, and it catches the one failure that would have got the sheet abandoned within a week. None of the other three would have taught you anything.

And that is why the ugly case goes in deliberately rather than waiting for it to turn up: when it turns up on its own it will be on a two-hundred-line sheet and you will not know why the counter is off.

And if your pharmacy is not like that

If you only want to control the fridge. Start there and it is the best decision: few items, high value and short dates, so the sheet repays the effort in weeks rather than months. And a fridge sheet that works is what makes you write the stockroom one.

If your software already flags expiry dates. Then do not duplicate: use this sheet only for what your software does not track — samples, orthopaedic items, health and beauty stock with no batch, whatever lives in the loft. Duplicating an existing control creates two truths that drift apart, and then you trust neither.

If you want the whole team filling it in. Google Sheets rather than Excel, because several people can write at once and there are no stray versions in email. And protect the formula columns: the first person who types over one breaks the whole column and nobody notices.

If entering the data feels like too much. Then combine it with the photo case: photograph the shelf with the packs facing forward and ask for the table with product, batch and expiry. It does not come out perfect and it is far faster than typing — and to start with, an imperfect sheet that exists beats a perfect one that does not.

When it does not work first time

The script asks for permissions that look alarming.

It asks to read your sheets and send email as you, because that is literally what it does. What is worth doing is reading the code before accepting, and that is where the "read only" instruction pays: if the script has no line that writes, write permissions are surplus and you can ask why it wants them.

Monday’s email does not arrive.

Check spam first, which is the commonest cause, and mark the sender as trusted. If it is not there, check the trigger was saved: in Apps Script you create it explicitly, and the script can be perfect and never run. And remember that if there is nothing urgent it sends no email — silence may be the good news.

I get an error pasting the script.

Paste the whole error message, literally. It is the same as in the formulas case and works just as well: the error gives the line and the reason, and with that it fixes it first time. Describing it in your own words is what to avoid.

The sheet has been green for two months and I do not trust it.

Quite right, and there is a way to know: deliberately add a row expiring next week and see whether it appears in Monday’s email. If it does, it works. If it does not, you have had a silent alert for two months believing there was nothing — which is exactly the ambiguity step 5 describes.

Before you use what it gave you

Dates are recognised as dates, not text.
You tested an expired one, an urgent one, a distant one and an empty cell.
You can change the 30 and the 90 yourself without breaking it.
The script only reads: it does not write to the sheet.
It sends no email when there is nothing to report.
You can tell "nothing is expiring" from "nobody is entering anything".

It gives Excel formulas for Google Sheets or the other way round. They look alike and are not the same; the argument separator — comma or semicolon — also changes with your locale. Say which you use in the prompt, and if a formula errors, give it the literal error instead of trying to fix it yourself.

It does not handle the empty cell. It is the commonest case in a real sheet and the least tested. A sheet that paints every half-filled line red stops being looked at within a week.

It writes you a script that also sorts and deletes. Without "read only" it tends to be helpful: it will sort your sheet, move expired rows to another tab or tidy it up. A script that writes to your sheet can write badly, and you find out when what it deleted is gone.

It writes the conditional formatting as though it were a formula. Conditional formatting is not pasted into a cell: it is configured in a menu, and the steps differ between Excel and Sheets. If it gives you something you cannot place, say "explain where I click, step by step, in [your program]" — it is the part of this case most people abandon and the one least to do with the formula.

Now, with your own. Start with one shelf and twenty products, this week. A small sheet that gets used grows on its own; a complete sheet that never gets finished is never used. And when the first Monday email arrives with something you would have binned, it has already paid for itself.

← All practical cases Back to the school →

An expiry sheet that checks itself

Step by step

Decide the columns before typing any

Ask for the formulas and for an explanation

Test it with three dates whose answer you know

Add the automatic alert, which is what makes it automatic

What this sheet does NOT replace

A full example, and what is wrong with it

And if your pharmacy is not like that

When it does not work first time

Before you use what it gave you