Medicine Refill Reminder in Excel: Tablets Left, Days of Stock and Refill-by Dates

By Ravindra Bagale · Last updated: 17 October 2026

Download the template: medicine-stock-refill-tracker-template.xlsx: example data with real formulas. Yellow cells are inputs you can type over; green cells hold formulas; blue rows are headers and totals. Replace the example data with your own and everything recalculates.

Made-up data: every name, amount, shop and date on this page is made up for practice. Cities are used only to make the examples feel real.

1. The example

As of 17-10-2026 (made-up medicines, packs and prices; a 5-day refill buffer):

The thyroid tablet runs out first: refill by tomorrow. To cover the next 30 days she needs 2 strips of the BP tablet and 1 of each of the others, a refill of ₹840. Her medicines cost about ₹941 a month in total.

2. Build it step by step

Step 1. As-of date and buffer

1. In B3 type =TODAY() (the template has 17-10-2026 typed). In B4 type how many days early you want to buy (5).

Step 2. Medicine list

1. In A6:L6 type the headers. Type each medicine, doses per week (once a day = 7, twice a day = 14, once a week = 1) and tablets counted today (yellow).
2. Select B7:B50 > Data Validation > List: 1,7,14,21 (data validation).

Step 3. Days left and dates

1. In D7 type =INT(C7*7/B7): tablets × 7 ÷ doses per week = full days of stock. INT drops the part day.
2. In E7 type =$B$3+D7: the date it runs out. In F7 type =E7-$B$4: refill by (date maths).
3. In G7 type =IF(D7<=7,"Refill now",IF(D7<=14,"Soon","OK")).

Step 4. What to buy

1. Type the pack size and price in H7:I7.
2. In J7 type =MAX(0,ROUNDUP((30*B7/7-C7)/H7,0)): tablets needed for 30 days minus stock, divided by the pack size, rounded up, never below 0 (ROUNDUP).
3. In K7 type =J7*I7 and in L7 =30*B7/7/H7*I7 (cost per month).

Step 5. Summary

1. In K12 and L12 type the SUMs: ₹840 to buy now, ₹941.43 a month.
2. In B15 type =INDEX(A7:A11,MATCH(MIN(D7:D11),D7:D11,0)): Thyroid tablet. In B16 =MIN(F7:F11): 18-10-2026.

Step 6. Colours

1. Select A7:G50 > New Rule > Use a formula: =$G7="Refill now" red (Stop If True), then =$G7="Soon" amber (formula rules).
2. Add Data Bars on D7:D50 with maximum 30.

3. Formulas used

Every formula above is written in the template, so you can click a cell and see it in the formula bar. All values on this page were recalculated in the file.

4. Common mistakes

1. Doses per day instead of per week. A weekly vitamin is 1 per week; per day it would be 0.14, which is easy to get wrong. Per week keeps all numbers whole.
2. Forgetting to recount. Update column C every week. Old counts make the dates wrong.
3. ROUND instead of ROUNDUP for packs. 1.4 strips means buying 2. ROUND would give 1 and you would run short.
4. Negative packs. If you have more than 30 days of stock, the formula would go below 0; MAX(0, …) stops that.
5. Treating the sheet as advice. The sheet only counts. Doses, changes and new medicines come from the doctor.

5. More real examples

Example 1: Two people at home. Add a For column (Rani, Ravindra) and filter, or keep one sheet per person.

Example 2: Pet medicines. The same columns work for a pet's monthly tablets.

Example 3: Travel check. Before a 10-day trip, change 30 to 10 in column J to see what to buy before leaving.

Quick revision cards

Englishमराठीहिंदी

Quick revision

Each card shows formulas from this page, each with an example and its answer from the template. Scan the QR code on a card to open this lesson. All names and numbers are made up for practice.

More cards: Ravi Sir's Cards.

6. Frequently asked questions

How do I calculate how many days my tablets will last?

Days left = tablets ÷ tablets per day. With doses per week: =INT(C7*7/B7).

How do I get a refill reminder date?

Runs out = today + days left; refill by = runs out − a few days of buffer.

How many strips should I buy for a month?

=MAX(0,ROUNDUP((30*doses per day − stock)/strip size,0)).

Can I track weekly medicines?

Yes. Doses per week = 1; the formulas stay the same.

Is this medical advice?

No. It only counts tablets and dates. Always follow the doctor and the prescription.

7. Quick quiz

Ten questions on this page. Sign in to save your score.