To track expiry dates in Excel you need a table with seven columns (company, item, type, expiry date, days left, status and owner), two formulas built on TODAY() and IF(), and three conditional formatting rules that colour whatever has expired or is urgent. It works as long as someone opens the file every day. If you look after several companies, or nobody checks it, it is time to move to automatic email reminders.
The template: seven columns
Copy this layout into a new sheet. Row 1 holds the headings, and every row after that is one thing that expires, not one company. A company with three lorries takes at least three rows.
| Row | A: Company | B: Item | C: Type | D: Expiry date | E: Days left | F: Status | G: Owner |
|---|---|---|---|---|---|---|---|
| 2 | Company 1 | Lorry 1 | ITV | 10/10/2026 | -7 | Expired | Ana |
| 3 | Company 1 | Driver Pedro | CAP card | 09/11/2026 | 23 | Urgent | Ana |
| 4 | Company 2 | Van 2 | Insurance | 31/12/2026 | 75 | Due soon | Luis |
| 5 | Company 2 | Director | FNMT certificate | 08/01/2027 | 83 | Due soon | Luis |
| 6 | Company 3 | Driver Marta | Class C licence | 20/03/2028 | 520 | Valid | Ana |
The days left and the status in the example are worked out as at 17 October 2026. In your sheet they update by themselves, because they come from a formula.
If the Spanish terms are new to you: the ITV (Inspección Técnica de Vehículos) is the periodic roadworthiness test, Spain's version of the MOT. The CAP (Certificado de Aptitud Profesional) is the professional driver qualification, carried on the driver qualification card; in the UK it is called the Driver CPC. The FNMT (Fábrica Nacional de Moneda y Timbre, the Spanish Royal Mint) issues the digital certificates that people and companies use to deal with the authorities online.
A few rules so the table doesn't fall apart over time:
- Type: always use the same words ("ITV", not sometimes "ITV" and sometimes "inspection"). That way you can filter.
- Expiry date: enter it as a date (10/10/2026), never as text ("October 2026"). A date stored as text breaks the formulas.
- Days left and status: never typed by hand. They are formulas.
- Owner: a named person, not "the office". If it belongs to everyone, it belongs to no one.
The formulas
The formulas below use English function names and commas between arguments. If your Excel is in Spanish, the same functions are HOY, SI, Y, CONTAR.SI and FECHA.MES, and the arguments are separated with semicolons, as in the syntax Microsoft publishes for the Spanish IF function (SI). If Excel rejects a formula with commas, swap them for semicolons.
Days left (cell E2):
=IF(D2="","",D2-TODAY())
The TODAY function returns the serial number of the current date. Because Excel stores dates as numbers, the expiry date minus today gives you the days left. A negative result means it has already expired. If column E shows a date instead of a number, change its format to Number.
Status (cell F2):
=IF(D2="","",IF(E2<0,"Expired",IF(E2<=30,"Urgent",IF(E2<=90,"Due soon","Valid"))))
Copy E2 and F2 down to the last row. The 30 and 90 day thresholds are only a suggestion: adjust them to how long each renewal takes.
Summary at the top of the sheet:
=COUNTIF(F:F,"Expired") and =COUNTIF(F:F,"Urgent")
Two numbers you see as soon as you open the file. If they are not zero, there is work to do.
Working out the next date. For anything on a fixed cycle, use the EDATE function, which returns the date a given number of months after another date. For example, =EDATE(H2,24) adds two years to the date in H2.
The ITV has a detail worth building into the formula. Article 6.5 of Royal Decree (Real Decreto, RD) 920/2017 says the new validity runs from the date the vehicle passed the inspection. But if you pass it within the 30 calendar days before the expiry date, it runs from the previous expiry date instead. With H2 as the date of the passed ITV, I2 as the previous expiry date and J2 as the number of months (24, 12 or 6):
=IF(AND(H2<=I2,I2-H2<=30),EDATE(I2,J2),EDATE(H2,J2))
The frequency depends on the vehicle's age, so check J2 when the vehicle moves into a new age band. For cards, licences and certificates, don't calculate anything: copy the date printed on the document itself.
Conditional formatting: let the sheet colour itself
This is how to create each rule, following Microsoft's guide to conditional formatting:
- Select the data range, for example A2:G500.
- Go to Home > Conditional Formatting > New Rule.
- Choose "Use a formula to determine which cells to format".
- Type the formula in "Format values where this formula is true".
- Click Format, pick the fill colour and click OK.
- Repeat for the other two rules.
| Colour | Formula | What it marks |
|---|---|---|
| Red | =AND($D2<>"",$D2<TODAY()) |
Expired |
| Orange | =AND($D2>=TODAY(),$D2-TODAY()<=30) |
Expires in 30 days or less |
| Yellow | =AND($D2-TODAY()>30,$D2-TODAY()<=90) |
Expires in 31 to 90 days |
The $ before the D locks the date column, so the whole row gets coloured. The three rules don't overlap, so their order doesn't matter. Microsoft points out that the formula must start with an equals sign (=) and return TRUE or FALSE, and that if a cell in the range returns an error, the formatting is not applied to it.
Common expiry dates and how often they come round
| Item | How often | Rule |
|---|---|---|
| ITV, car (M1) | Exempt up to 4 years; every 2 years from 4 to 10; every year after 10 | RD 920/2017, art. 6.1 |
| ITV, goods vehicle up to 3,500 kg (N1) | Exempt up to 2 years; every 2 years from 2 to 6; every year from 6 to 10; every 6 months after 10 | RD 920/2017, art. 6.1 |
| ITV, lorry over 3,500 kg (N2, N3) | Every year up to 10 years; every 6 months after 10 | RD 920/2017, art. 6.1 |
| Driver qualification card (CAP) | 5 years, with a 35-hour continuing training course | RD 284/2021, arts. 7 and 20 |
| Tachograph inspection | At least once every two years | Regulation (EU) 165/2014, art. 23 |
| Tachograph driver card | No more than 5 years | Regulation (EU) 165/2014, art. 26.6 |
| Licence C, C+E, D and similar | 5 years up to age 65; 3 years after | RD 818/2009, art. 12 |
| Licence B and other classes | 10 years up to age 65; 5 years after | RD 818/2009, art. 12 |
| FNMT personal certificate | The date shown on the certificate | FNMT |
| FNMT representative certificate | 2 years | FNMT |
| Insurance | Whatever the policy says; renewals of one year at most | Law 50/1980, art. 22 |
ITV
The vehicle's age counts from the date of first registration recorded in the Vehicle Register kept by Tráfico, the Spanish traffic authority (RD 920/2017, art. 6.1). Taxis, ambulances and school transport have their own schedule: every year up to 5 years old and every 6 months after that. Thanks to the 30-day rule in article 6.5, bringing the ITV forward within that margin doesn't lose you any days.
CAP card
The driver has to pass a 35-hour course every five years. It can be done in blocks of at least 7 hours, with the same CAP training provider and within the same calendar year (RD 284/2021, art. 7.2). The card is issued for five years and, under article 20.1, it "shall be deemed expired, unless the driver completes a continuing training course before the twelve months preceding the expiry date of their card, in which case the five years of validity are counted from the date on which the course was passed" (our translation). In practice, 35 hours of training have to be booked well ahead, so the reminder has to arrive months before.
Tachograph
Inspections are carried out by authorised workshops, at least once every two years (Regulation (EU) 165/2014, art. 23.1). Record each driver's tachograph driver card as well: its validity cannot exceed five years (art. 26.6).
Driving licence
You can apply to renew it up to three months in advance, and the new period runs from the expiry date, not from the day you apply (RD 818/2009, art. 13.1). You cannot drive on an expired licence (art. 12.4).
FNMT digital certificate
- Personal certificate (persona física). The date is on the certificate itself. The FNMT says those issued by AC FNMT Usuarios are valid until 31 December 2028, and its certification policies set a maximum of 4 years for those issued under the new G2 hierarchy. It can be renewed in the 60 days before it expires. Once it has expired, it cannot be renewed. And if it was obtained with another certificate or with the DNIe (the Spanish electronic ID card), or has already been renewed once, you have to prove your identity in person at a registration office.
- Representative certificates. All three types last 2 years. The legal entity certificate (persona jurídica) is not renewed: when it expires you have to apply for a new one. The certificate for a sole director or a joint and several director (administrador único o solidario) cannot be renewed online for now.
Insurance
The policy sets the term, with renewals of one year at most each time. If you want to switch insurer, you have to object to the renewal in writing at least one month before the end of the period; the insurer needs to give two months' notice (Law 50/1980 on Insurance Contracts, art. 22). That is why, with insurance, the date that matters is not the expiry date but one month before it.
If you manage communities of owners, the building's own inspections run on a separate calendar: we cover it in mandatory building inspections in Spain.
How much warning to give
| Item | Warn | Why |
|---|---|---|
| ITV | 30 days or more | Within the 30 days before expiry you lose no validity (RD 920/2017, art. 6.5) |
| Driving licence | 3 months | The earliest you can renew without losing days (RD 818/2009, art. 13.1) |
| FNMT personal certificate | 60 days | It is the renewal window; once expired it can't be renewed |
| Insurance | More than a month | So you can object to the renewal in time (Law 50/1980, art. 22) |
| CAP card | Several months | The 35 hours of training have to be booked and completed |
In the sheet, that means one more column with the warning days for each type, and a rule that compares E2 with that number.
The honest limits of a spreadsheet
The template works. Its problems are not about formulas, they are about how it gets used:
- Nobody opens it. The sheet only changes colour when someone looks at it. The busiest day is exactly the day nobody opens it.
- It doesn't warn you. An ordinary sheet won't send you an email. For that you need macros or another tool, and that is one more system to maintain.
- Versions. "tracker_final_v2.xlsx" in an email, another copy on someone's desktop. Two people update different copies and neither is complete.
- Everything in one file. If you keep several companies in the same file and send it to a client, they see everyone else's dates.
- Formulas that break. One row pasted without the formula, or one date typed as text, and that line stops warning you without anyone noticing.
- No history. You don't know who changed a date, or when.
When to switch to automatic reminders
Excel will do if you track a few dates for a single company and one person really does open it every morning. It is worth automating when:
- You track dates for several companies, as a gestoría (an accounting and admin agency) or a property manager does.
- Everything depends on one person, and when they go on holiday nobody looks at the sheet.
- Forgetting is expensive: an expired FNMT certificate can't be renewed, so you have to apply for a new one, and a driver with an expired licence can't drive (RD 818/2009, art. 12.4).
- Checking the sheet has become one more daily chore.
That is why we built Renewals. It checks every date by itself each morning and emails you before an ITV, an insurance policy, a CAP card or a service check expires, company by company. Each company is isolated inside the database, so one never sees another's data, and the calendar view shows you what is coming. If you run an advisory firm, see also how we approach automation for advisory firms and for property managers.
If you would rather stick with Excel, the template above is enough to get started. What matters is that someone's name is in column G and that they open the file every day.
Sources
- Royal Decree 920/2017 of 23 October, regulating vehicle roadworthiness inspections (consolidated text, BOE, in Spanish)
- Royal Decree 284/2021 of 20 April, initial qualification and continuing training of drivers (consolidated text, BOE, in Spanish)
- Regulation (EU) No 165/2014 on tachographs in road transport (BOE, in Spanish)
- Royal Decree 818/2009 of 8 May, General Drivers Regulations (consolidated text, BOE, in Spanish)
- Law 50/1980 of 8 October on Insurance Contracts (consolidated text, BOE, in Spanish)
- FNMT: validity period of the personal certificate (in Spanish)
- FNMT: Specific Certification Policies and Practices for Citizens' Certificates, version 2.0 (in Spanish)
- FNMT: when to renew the personal certificate (in Spanish)
- FNMT: validity period of representative certificates (in Spanish)
- FNMT: terms and conditions of the legal entity representative certificate (in Spanish)
- FNMT: renewing the sole or joint and several director certificate (in Spanish)
- Microsoft: TODAY function
- Microsoft: IF function and its Spanish version (SI)
- Microsoft: EDATE function
- Microsoft: Use conditional formatting to highlight information in Excel