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.
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:
- 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.
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.