How to track expiration dates in Excel

Step 1: Get the expiry date (EDATE does the math)

You look after the office's certificates, licenses and contracts. Something always lapses because nobody noticed. A sheet with an Issued date and a Valid (months) column fixes that: Excel works out the expiry date for you.

=EDATE(C2,D2) adds the months to the issue date. A first aid certificate issued Nov 20, 2024 and valid for 24 months expires Nov 20, 2026. Format the column as a date, or you'll see a serial number like 46346.

Watch the month ends. EDATE on Jan 31 plus 1 month lands on Feb 28, because February has no 31st. If a certificate is "valid until the day before", use =EDATE(C2,D2)-1. For contracts that end on the last day of a month, =EOMONTH(C2,D2) is the cleaner choice.

Step 2: Days left, from an as-of date

Subtract the as-of date from the expiry date. This sheet keeps the as-of date in H1 (Nov 5, 2026) so the numbers here stay the same. In your file, put =TODAY() in H1 and every row updates each time you open it.

Positive numbers are days to go, negative numbers are days past. The first aid certificate has 15 days left, the food handler card is 21 days past. Lock the as-of cell with $H$1 so it stays put when you fill down.

Step 3: A status column with the warning window in a cell

Numbers are good for sorting. Words are better for a quick look. IFS checks the conditions in order and stops at the first one that's true, so Expired must come first.

Keep the warning window in its own cell (H2 holds 30 days) instead of typing 30 into the formula. Need 60 days' notice for contracts? Change one cell. The vehicle insurance with 35 days left shows OK now and Expiring soon the moment you set H2 to 60.

If some rows have no date yet, wrap it: =IF(E2="","No date",IFS(...)). Otherwise an empty date counts as day zero in 1900 and shows Expired. IFS needs Excel 2019 or later; in older versions, nest IF instead.

Step 4: Count them, and list what expires next

A summary at the top saves scrolling. =COUNTIF(E2:E9,"<"&H1) counts expired items: 2. The formula below counts the ones expiring within the window: 3. The "&" glues the comparison sign to the date in the cell.

In Excel 365, FILTER and SORT give you the actual list, soonest first: the first aid certificate, the design software license and the forklift license. =XLOOKUP(MIN(FILTER(E2:E9,E2:E9>=H1)),E2:E9,A2:A9) names the single next item to expire.

Step 5: Color the rows

Conditional formatting makes the sheet scream at you before anything lapses. One rule for red, one for amber. The $ before E keeps every cell in the row looking at the date column.

Home > Conditional Formatting > Highlight Cells Rules > A Date Occurring has options like Next Month, but those are calendar periods, not "the next 30 days". The formula rules below follow your warning cell instead.

1. Select A2:E9 (start from A2).
2. Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
3. Enter =$E2<$H$1, choose a red fill, click OK.
4. Add a second rule: =AND($E2>=$H$1,$E2-$H$1<=$H$2) with an amber fill.
5. Sort by the Expires column (Data > Sort) so the urgent rows sit at the top.