Building a warranty tracker in a spreadsheet

Why a spreadsheet is a reasonable starting point

A spreadsheet handles the part that matters most: it recalculates every time you open it. Enter a purchase date and a warranty length once, and the expiry date, the months remaining and the status all stay current without you touching them again. That is the whole trick, and it is why this method works far better than a written list.

It has real limits — attaching receipt photos is awkward, it does not fit a phone screen well, and there is no reminder mechanism — but the logic is sound, and it is worth understanding before you decide what to use.

Step 1: the columns

Nine columns, in this order. The first five you type; the next three are formulas; the last you type.

Column	Heading	Type or formula
A	Item name	Typed
B	Brand / model	Typed
C	Serial number	Typed
D	Purchase date	Typed, as a real date
E	Warranty period	Typed, in months
F	Warranty expiry date	Formula
G	Months remaining	Formula
H	Status	Formula
I	Receipt / invoice number	Typed

Why column I exists

Keeping dozens of paper receipts findable for years is not realistic. Recording the invoice number is the compromise: it is short, it does not fade, and it is usually enough for a seller to reprint the original from their system. Combine it with a photograph of the bill and you have a complete record. See storing receipts.

Step 2: warranty period in months

Column E is always months. A two-year warranty is 24, not 2. Mixing units in one column is the single most common way these sheets go wrong, and the error is invisible — 2 and 24 both look like legitimate entries.

To make the unit visible without affecting the number, apply a custom number format to column E:

# "Months"

The cell now displays 24 Months while still containing the number 24, so the formulas keep working. Select the column, open the format-cells dialog, choose Custom, and enter that pattern.

Step 3: the expiry date (column F)

The core formula. Build it up in stages so the logic is clear.

Stage 1 — add the months

=EDATE(D2, E2)

EDATE adds a number of months to a date. Use it rather than adding 30 days per month, because it handles the end of short months correctly: one month after 31 January is 28 February, not 3 March. Adding raw days does not do this and will drift.

Stage 2 — subtract one day

=EDATE(D2, E2) - 1

This is the step almost everyone leaves out. The warranty ends the day before the purchase anniversary, because the anniversary is the first day no longer covered. Bought 2 December 2023 with 24 months, you are covered to 1 December 2025. The full reasoning is here.

Stage 3 — handle empty rows

=IF(E2="", "", EDATE(D2, E2) - 1)

Without this, every blank row below your data shows a spurious date computed from zero, which looks like a real entry and clutters the sheet.

Enter the finished formula in F2 and fill it down.

Step 4: months remaining (column G)

Again in stages.

Stage 1 — the difference in whole months

=DATEDIF(TODAY(), F2, "M")

DATEDIF returns the number of complete months between two dates. The "M" asks for months. Because it counts complete months, 29 days remaining shows as 0 — which is the honest answer to "how many more months am I covered for".

The result may appear as a date. If so, select the column and set its format to General or Number.

Stage 2 — never show a negative

=MAX(0, DATEDIF(TODAY(), F2, "M"))

An expired warranty would otherwise show a negative count, which reads oddly in a column headed "months remaining".

Stage 3 — label expired rows

=IF(F2 < TODAY(), "Expired", MAX(0, DATEDIF(TODAY(), F2, "M")))

Now anything past its date says so in words rather than showing a bare zero, which is ambiguous — zero could mean "less than a month left".

Stage 4 — suppress errors

=IFERROR(IF(F2 < TODAY(), "Expired", MAX(0, DATEDIF(TODAY(), F2, "M"))), "")

DATEDIF returns an error when its arguments are blank or inverted, and those errors propagate visually down the whole sheet. Wrapping in IFERROR leaves empty rows genuinely empty.

Enter this in G2 and fill down.

Step 5: status (column H)

Three states: expired, close to expiry, and comfortably in warranty. Nested IF statements, built outward:

=IF(G2 = "", "", IF(G2 = "Expired", "Expired", IF(G2 < 1, "Near Expiry", IF(G2 = 1, "Near Expiry", "In Warranty"))))

Reading it in order:

• If column G is blank, the row is empty — return blank.
• If G says "Expired", carry that through.
• If fewer than one whole month remains, or exactly one, flag it as near expiry.
• Otherwise it is comfortably in warranty.

Enter in H2 and fill down.

Step 6: colour coding

Colour is what turns a table into something you can read at a glance. Three rules.

Rules 1 and 2 — the status cell

Select column H from the first data row to the bottom, then add conditional formatting rules of the "cell value equals" type:

• In Warranty → green fill, white text.
• Near Expiry → amber fill, black text.

Black text on amber is not a stylistic preference. White on amber is genuinely hard to read, and this column exists to be read quickly.

Rule 3 — the whole row, for expired items

An expired item deserves more than a coloured cell; it should be impossible to scroll past. Select the full data range A2:I200 (or wherever your data ends), then add a new conditional formatting rule using a formula:

=$H2 = "Expired"

Set it to a red fill with white text.

Step 7: the summary counters

Above the table, three counts so you can see the position without reading the rows. Assuming your status column runs H2:H200:

• =COUNTIF($H$2:$H$200, "Expired")
• =COUNTIF($H$2:$H$200, "Near Expiry")
• =COUNTIF($H$2:$H$200, "In Warranty")

Lock the range with dollar signs so it does not shift when you copy the formula sideways, and extend the range well past your current data so new rows are counted automatically.

Step 8: today's date at the top

Three cells, each containing =TODAY(), with different custom formats applied:

Format	Shows
dddd	The day name, e.g. Thursday
dd	The day number, e.g. 22
mmmm	The month name, e.g. October

It sounds decorative, but it earns its place: it confirms at a glance that the sheet has actually recalculated. If that date is stale, every status below it is stale too.

What this method does well, and where it stops

Does well: the arithmetic, the daily recalculation, the visual triage, the counters. Sorting and filtering. Handing a copy to someone else.

Stops short at:

• Receipts. You can link to files, but the links break when files move, and nothing keeps the photograph beside the record.
• Reminders. The sheet knows something expires next week. It has no way to tell you unless you open it, and the whole problem is that you do not.
• Phones. A nine-column sheet is unusable on a phone, which is where you are standing when you unpack something.
• Fragility. One stray edit to a formula cell breaks a column silently. These sheets degrade over time as they get edited by hand.
• Search. You can filter by column, but you cannot type a serial number and have it find the row.

---

Whichever you use, the method is the same and the method is what matters: store the length in months, compute the end date as purchase plus months minus a day, recalculate it every day, and make the three states visible without reading.