To plan refills for several family members, list each medicine with its quantity, doses per day and purchase date, then calculate when it runs out and when to order. Sorting by the order-by date shows which refills fall close together, so a single trip or delivery covers them.
Why one calendar beats separate reminders
When each person has their own medicines and their own refill dates, the pharmacy is visited again and again, or a delivery is placed whenever someone notices a strip is nearly finished. Putting every refill on one sheet turns it into a planning job. You see that three medicines run out within a week of each other and order them together.
The columns
- A Person
- B Medicine, as on the prescription
- C Tablets bought
- D Doses per day
- E Date bought
- F Days covered, calculated
- G Runs out on, calculated
- H Order by, calculated, a few days before it runs out
Put the number of buffer days in a cell, say K1. Three or four days is a reasonable starting point, but choose what suits your supplier and delivery time.
The formulas
These work in Excel and Google Sheets. Dates are stored as numbers, so adding days works directly. Enter the row 2 formulas and copy down:
- Days covered in F2: =C2/D2
- Runs out on in G2: =E2+F2
- Order by in H2: =G2-$K$1
- Flag for the coming week in I2: =IF(H2<=TODAY()+7, "Order soon", "")
Format E, G and H as dates. If a package is partly used when you start the sheet, enter the quantity you have left and today's date as the date bought.
A worked example
Say the buffer is three days. Dates and quantities below are invented.
| Person | Medicine | Tablets | Doses per day | Bought on | Runs out on | Order by |
|---|---|---|---|---|---|---|
| Elder A | Medicine A | 30 | 1 | 3 Nov | 3 Dec | 30 Nov |
| Elder A | Medicine B | 60 | 2 | 8 Nov | 8 Dec | 5 Dec |
| Elder B | Medicine C | 28 | 1 | 12 Nov | 10 Dec | 7 Dec |
| Adult | Medicine D | 90 | 1 | 20 Oct | 18 Jan | 15 Jan |
Medicine A: 30 tablets at one a day is 30 days, so 3 Nov plus 30 days is 3 Dec. Medicine B: 60 tablets at two a day is 30 days, so 8 Dec. Medicine C: 28 days from 12 Nov is 10 Dec. Medicine D: 90 days from 20 Oct is 18 Jan.
The first three run out within a week of each other, between 3 and 10 December. One order placed around 30 November would cover all three. The calendar tells you that. Medicine D stands alone, so leave it for a later order.
How do I handle a delivery order?
If you order online, work back from the delivery time. Add the typical delivery days to the buffer, or increase the buffer cell. Pick one day a month as the standing order day, and let the sheet tell you what to include. Anything that runs out before the next order day goes in this order.
Where does the spending record come in?
The refill sheet plans purchases. Your tracker records them. In SpendixAI the pharmacy payments appear when you import your statement, and you can add a cash purchase by hand and keep the bill with it. It does not manage refill dates, so the sheet stays the planner, and the app is the record of what was paid.
Keep it working
- Update the row whenever you buy, replacing the date and the quantity
- Add a new row when a new medicine starts, and mark stopped ones rather than deleting
- Review the sheet on a fixed day each month
- Keep each person's prescription date in a column if renewals are needed
Useful ideas start with a clearer picture of your own spending.
Explore your reports
