Expiry Dates in Excel: Days Left and Status Flags

A good expiry date formula in Excel answers three questions: when does it expire, how many days are left, and does it need attention? EDATE works out the date, TODAY counts the days, and IF turns the result into a clear flag like Expired or Expiring soon. This guide builds a certificate tracker step by step. The same formulas work for contracts, licences, stock and subscriptions.

How to calculate an expiry date from an issue date#

Add the validity period to the issue date. Which formula you need depends on how the period is stated:

EDATE adds whole months and keeps the same day of the month, which matches how most certificates and contracts are written. Adding days works because Excel stores dates as numbers that count days. EOMONTH suits items like payment cards that expire at the end of a month.

If the validity varies by row, put the number of months in its own column and use =EDATE(B2,E2). If the result shows a number like 46346, format the cell as a date (Windows: Ctrl+1; Mac: ⌘1).

How to count the days left until expiry#

Subtract TODAY() from the expiry date. TODAY returns the current date and updates whenever the file recalculates:

=C2-TODAY()

On 1 November 2026, the certificate tracker shows:

A negative number means the item expired that many days ago. If you'd rather show 0 for expired items, use =MAX(0,C2-TODAY()). The TODAY function guide explains how it recalculates, and counting the days until a deadline covers countdowns in more depth.

How to flag Expired, Expiring soon and OK#

Use nested IF to turn the dates into status words. IF tests a condition and returns one value if it's TRUE and another if it's FALSE. Nesting puts a second IF in the FALSE part:

=IF(C2<TODAY(),"Expired",IF(C2-TODAY()<=30,"Expiring soon","OK"))

Read it in order:

1. Is the expiry date before today? Then Expired.
2. If not, is it 30 days away or less? Then Expiring soon.
3. Otherwise, OK.

On 1 November 2026, First aid (19 days) shows Expiring soon, the Forklift licence shows Expired, and the other two show OK. Food hygiene is 34 days away, just outside the 30-day window.

The order of the tests matters. An expired item also has fewer than 30 days left, because its days-left number is negative. If you test "30 days or less" first, expired items show Expiring soon. Always check Expired first. The IF function guide and the guide to IF with dates cover more patterns.

In Excel 2019 or later, IFS reads more simply:

=IFS(C2<TODAY(),"Expired",C2-TODAY()<=30,"Expiring soon",TRUE,"OK")

The final TRUE,"OK" catches everything else. IFS checks the tests from left to right and stops at the first TRUE, so the same rule applies: put the Expired test first.

Using a warning period from a cell#

Teams often want different warning periods, such as 60 days for licences that take time to renew. Put the number of days in a cell, for example H1, and use it in the formula: =IF(C2<TODAY(),"Expired",IF(C2-TODAY()<=$H$1,"Expiring soon","OK")). Change H1 and every status updates at once.

How to handle empty rows#

Add a blank check at the start, so empty rows stay empty. An empty expiry cell counts as 0, which is earlier than today, so the status formula would call it Expired:

=IF(C2="","",IF(C2<TODAY(),"Expired",IF(C2-TODAY()<=30,"Expiring soon","OK")))

This matters in trackers where you leave rows ready for new items. The same idea applies to the days-left column: =IF(C2="","",C2-TODAY()).

How to highlight expired and expiring items#

Use conditional formatting with the same tests, so colours update every day. Select the table rows, for example A2:D50, and add two rules with Home > Conditional Formatting > New Rule > Use a formula:

The $ before C makes every cell in the row check the expiry date in column C, so the whole row is coloured.

If the tracker has a status column, you can also format by the word itself: Highlight Cells Rules > Text that Contains > Expired. That keeps the logic in one place.

How to count expired and expiring items#

Use COUNTIF and COUNTIFS to put a summary at the top of the tracker. Join the operator to TODAY with &:

If you have a status column, =COUNTIF(D2:D5,"Expired") is even simpler.

The same structure works for many trackers. A job application tracker counts days since you applied, and an order tracker flags late deliveries.

Common mistakes with expiry formulas#

Most problems come from test order, fixed dates or blank cells. Check these:

1. Testing "soon" before "expired". Expired items then show as Expiring soon.
2. Typing today's date by hand. A fixed date never updates. Use TODAY() in formulas. Use Ctrl+; (Mac: Control+;) only for dates that should stay fixed, such as the issue date; see inserting the current date with a shortcut.
3. Adding 365 for a year. Leap years make it drift. Use EDATE with 12 months.
4. Blank rows marked Expired. Add the C2="" check.
5. Days left shows a date. Format the cell as General.

Practise it#

Expiry trackers combine date maths and IF logic, a pairing that comes up in many job tests. Practise counting days between dates in a live spreadsheet, then practise the IF function to build status flags. Each formula is checked instantly. Or try the exercise on this page first.