Monitor Expiry Dates in Excel

Monitor Expiry Dates in Excel

Do you use Excel to keep track of software subscriptions, or domain registrations, or other things with an expiry date? It’s important to keep track of those dates, so here’s an example of how to monitor expiry dates in Excel, and see what needs to be renewed soon.

Expiry Dates List

Here’s a simple list of expiry dates for Microsoft Office subscriptions. Those have to be renewed every year, so that you don’t arrive at the office one day, and find out that you can’t use Excel. Oh, the horror!

How Many Days to Expiry Date

To make it easy to see which subscriptions are expiring soon, I’ll use conditional formatting to highlight anything that will expire within the next 30 days.

I could make a conditional formatting rule that is based on the Expiry Date column, but I find it easier to check regular numbers, instead of dates.

So, I’ll add a new column, with a formula to calculate the number of days there are before the expiry date. In the screen shot below, you can see that new column – DaysToExpiry.

The formula in that column F subtracts the current date from the Expiry Date: =D3-TODAY()

The results look a bit strange though, when you hit the Enter key, to add that formula.

Fix the Results

Because the formula refers to a date cell, Excel “helps” us, by formatting the result as a date too.

A couple of the rows have expiry dates in the past, and those rows show number signs in the DaysToExpiry column. Excel can’t convert negative numbers to dates, so it shows those number signs instead.

To fix all the results:

  • Click in the heading of the DaysToExpiry column, to select all the table rows.
  • On the Home tab of the Excel Ribbon, choose General or Number as the Number Format.

Highlight Upcoming Expiry Dates

To make the upcoming expiry dates stand out, we’ll use conditional formatting. Based on the result in the DaysToExpiry column, we can highlight the expiry dates which are 30 days (or less) from today.

  • Tip: There’s a video further down on this page, that shows how to set up conditional formatting in a row, based on the values in a specific column.

To add the conditional formatting:

    • Select all the data rows in the expiry date table
    • On the Home tab of the Excel Ribbon, click Conditional Formatting, then click New Rule
    • In the New Formatting Rule window, in the “Select a Rule Type” section, click “Use a formula to determine which cells to format”
    • In the Rule Description section, type this formula in the formula box:
      • =$F3<=30
      • NOTE: The formula uses an absolute reference to column F (DaysToExpiry), and a relative reference for the row number.
    • Click the Format button, and choose a fill colour to highlight the rows with upcoming Expiry dates

  • Click OK to close the Format window, then click OK to close the New Rule window

The table rows with upcoming expiry dates are highlighted with the colour that you selected.

More Expiry Date Warnings

With conditional formatting on the expiry date list, you’ll quickly see which subscriptions need to be renewed soon. Just open the workbook every day, and renew those subscriptions, as soon as they’re highlighted. Then, remember to change the Expiry Date, so you’ll get a warning next year too.

If you want additional warnings, my sample file also shows how to make a hyperlink appear, in a SendEmail column. You could click that to send an email about the expiring subscription, if someone else is supposed to renew it. Or, have a link to the website where you go to renew the subscription.

Expiry Dates Summary

My sample file also has a Summary sheet — it shows the total count of expiry dates, and the number that are expiring soon.

If you don’t want to open the Expiry Dates workbook every day, you could link those summary cells to another workbook – one that you DO open every day.

Video: Conditional Format Based on Cell Value

This short video shows the steps for setting up conditional formatting in a row, based on the value in a specific column.

Download the Sample File

Get the sample Excel file for this tutorial from my Contextures website. Go to the Excel Sample Files page, and in the Conditional Formatting section, look for CF0008 – Expiry Date Warning. The zipped file is in xlsx format, and does not contain any macros.

And while you’re on the Sample Files page, take a look around. There are lots of other files there – you might find some other interesting techniques to try.

________________________

Monitor Expiry Dates in Excel

More Excel Date Tips

Fix Excel dates that won’t change Format

AutoFill Excel dates in series or same Date

How to prevent grouped dates in Excel

Excel filter for date range 2 Ways

Count items in a date range in Excel

________________________

11 thoughts on “Monitor Expiry Dates in Excel”

  1. Hi
    I have found this very useful, however I am trying to set up either a report or email alert on multiple expiries over several column and rows on a worksheet.
    is this possible?

    Kind regards

  2. I am trying to set up an data base that notifies me when insurances are expiring, I have tried to use your information and resources to create mine-I cannot make the summary page work -that would show instant totals of expired policies; as apparently I do not know enough, and the directions seem to skip all the tiny necessary steps….can I see a more in-depth resource on this?
    Totally frustrated and wanting to learn!

  3. I got to the point where I am able to get the date, convert it to regular numbers, but when I apply the conditional formatting to simply highlight it with a color of my choice, it logically does not do so. And the funny thing is, it highlights some other numbers instead. I even tried it on a new Excel worksheet with very small amount of data but still same. I wonder what I am doing wrong.

    1. You cannot do it, because there is mistake in the formula. To highlight number less than 30 (in this example) you have to use “>” instead of “=30. This is a very useful topic (thank you to Author), but with one mistake. 🙂

  4. HI I am using Smartshseets and i have to apply conditional formatting on the date like if i have set whatever date i used after 14 days they automatically changed color.

    can you help me how can i do that?

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.