How to Highlight Expiry Dates in Excel (Step-by-Step)

Expiry dates are not best before dates and carry a higher element of urgency. Expiry dates guide the course of action for saving the articles from expiring. Perishable goods, subscriptions, registrations; some things will need to be renewed, the others disposed of. And keeping a track of things has never been a bad idea.

Working on that good idea, today's tutorial features how to highlight expiry dates in Excel. The same concept can be applied to overdue dates. Learn how to find and highlight past expiry dates and upcoming expiry dates.

The highlighting will be done using Conditional Formatting. Conditional Formatting in Excel is a convenient tool that can identify and highlight important values or trends using preset and/or user-defined conditions.

Let's understand a case example. Below is a dataset enlisting a batch of granola bars with the price per bar and the expiry date of each type of bar. Expiry dates are relevant in relation to the current date. We have, therefore, added the current date in cell D2 using the TODAY function.

highlight-expiry-dates-in-excel_01

At this point, we want to find out the items that are past their expiry dates, to remove them from the stock. It would also be helpful to find items that are about to expire in order to take timely action. We think we should start with the most urgent matter of finding the items way past their prime.

Uncap those fluorescent highlighters. Let's get marking!

How To Highlight Expiry Dates In Excel

Highlight Expired Dates

First, we'll be looking at how to highlight expired dates. Those will be items that have expiry dates earlier than today. Hence, the aim is to find and highlight the dates before the current date. We will achieve that by creating a new Conditional Formatting rule based on a formula. The steps for highlighting expired dates, with our case example, are mentioned below.

  • Select the cells containing the dates.
  • Our case example has the expiry dates in column D, so our cell selection is D5 to D20 as the first step.
  • In the Home tab, click on the Conditional Formatting button in the Styles group and select New Rule from the menu.
  • Highlight Expired Dates
  • In the dialog box, select the last rule type i.e., Use a formula to determine which cells to format. In the text box, enter this formula:
  • =D5<TODAY()

    D5 is the first cell in the selection on the worksheet (edit as per your dataset). This formula will spot the dates from column D that are less than today. Today refers to the current date added using the TODAY function.
    Any date less than today means before today and when that date is found, this formula returns TRUE, and then Conditional Formatting highlights that cell. The last thing you need to set the new rule is the format for the highlight.

  • This is how the expiry dates will be highlighted. We have chosen a light red color fill by clicking on the Format button.Highlight Expired Dates
  • With all three elements set, click on the OK button in the dialog box.

The rule will be created, the dialog box will close, and you will return to the worksheet. The rule will be applied, highlighting the expired dates:

Highlight Expired Dates

We can easily note that all the dates occurring before 31/01/2023 have been highlighted in the set format.

Highlight Expired and Close-to-Expiry Dates

If your data has all later dates without any past dates, you may only be interested in highlighting upcoming expiry dates and we will expound on that later on. Taking a look at our dataset, it makes little sense to only check close to expiry dates without checking expired dates since the data includes past dates.

Let's assume we're checking items that are up to a month away from expiry in addition to those that have already expired. Making use of Conditional Formatting again, here's how to highlight expired and close-to-expiry dates:

  • Select the date cells.
  • Go to the Home tab > Styles group > Conditional Formatting button > New Rule
  • Choose the last Rule Type in the dialog box and set the format for the highlight cells (light red in our case). In the Format values where this formula is true field, copy-paste this formula:
  • =D5<=TODAY()+30

    The formula will highlight all dates in column D that are less than and equal to 30 days from today. This will consider all items that are a month away from expiry and also those that have expired earlier than the current date.Highlight Expired and Close-to-Expiry Dates

  • Hit the OK command of the dialog box.

All dates occurring before 30 days from 31/01 are highlighted using the above rule. We can see two more dates have been included in the count along with all the expiry dates we saw highlighted in the previous section:

Highlight Expired and Close-to-Expiry Dates

Highlight Upcoming Expiry Dates

If you're finding only the upcoming expiry dates, that means the expired dates (the ones occurring earlier than today) will have to be left out. Identifying close-to-expiry items is helpful for well-timed usage, sales, discounts, other approaches for clearing stocks, or preparing for subscription renewal.

All we need is a change in the formula of our Conditional Formatting rule and our job will be done. The following steps will guide you on how to highlight upcoming expiry dates with Conditional Formatting:

  • Select the cells with the dates in your dataset.
  • Click on the Conditional Formatting icon in the Home From the menu, select Manage Rules.
  • Highlight Upcoming Expiry Dates
  • We're accessing the Conditional Formatting Rules Manager to create multiple rules easily and you'll see why in a bit.
  • Click on the New Rule button in the Rules Manager.
  • Highlight Upcoming Expiry Dates
  • You will be redirected to the New Formatting Rule dialog box.
  • Make these changes in the dialog box:
  • Select the last Rule Type to add a formula.
  • Set the format using the Format button (we have set the color fill as light red).
  • Add this formula in the provided field:
=AND(D5>TODAY(),D5<=TODAY()+30)

As in the previous section, the second part of this formula proceeding the comma deals with the dates occurring before 30 days from today. If we leave it at that, the dates before today will also be included. To stop that, we've added another condition in the formula; the dates highlighted have to also be greater than today. This is effective for keeping the search to later than the current date and up to 30 days ahead.

Since we want the highlighted dates to be based on both these conditions, we have wrapped the criteria in the AND function. Therefore, Conditional Formatting will not highlight a date until both these conditions are met.

Edit the formula befitting the data you have. Change "30" in the formula to the number of days you want. E.g. for expiry dates up to a week ahead, use "7". Change D5 to the first cell of your cell selection.

Highlight Upcoming Expiry Dates
  • To seal the rule in, click on OK.
  • You will be back to Rules Manager where the new rule will be listed:
  • Highlight Upcoming Expiry Dates
  • Keep the Rules Manager open to proceed with the next rule. We'll give you a sneak peek at how this rule has worked on the data:
  • Highlight Upcoming Expiry DatesBack to the Rules Manager, we will create 2 more rules from here. In addition to finding expiry dates 30 days from now, we're also going to highlight dates from 60 days and up to 90 days from today. To accomplish that, go through the steps given next:
  • In the Rules Manager, use the New Rule button again to create another rule.
  • Use the formula Rule Type. Enter this formula in the text box:
  • =AND(D5>=TODAY()+31,D5<=TODAY()+60)

    In the previous rule, the range of dates we kept was greater than today and up to 30 days ahead. The next range will be greater than equal to 31 days and up to 60 days ahead. These are the two conditions this time, both of which are fed to the AND function for highlighting the dates.Since this range of dates is less urgent than the one in the previous rule, we can highlight the dates that will fall under this band in yellow color fill.Highlight Upcoming Expiry Dates

  • When done, use the OK button to create the rule.

The second rule will be created. Onto the third:

  • Select the New Rule button again in the Rules Manager to form the third rule.
  • In the New Formatting Rule dialog box, change the Rule Type to Use a formula to determine which cells to format. This will be the least urgent date range so we'll set the format to a green color fill. Lastly, copy-paste the formula for the rule in the dialog box:
  • =AND(D5>=TODAY()+61,D5<=TODAY()+90)
  • Continuing from the date range of the previous rule, this formula will highlight dates from 61 days from today to 90 days from today.
  • Highlight Upcoming Expiry Dates
  • Hit the OK button one more time and you should see all three rules in the Rules Manager.
  • Highlight Upcoming Expiry Dates
  • Click on OK in the Rules Manager.

This is how the three rules have highlighted the expiry dates as per their respective date ranges:

Highlight Upcoming Expiry Dates

There are 3 types of granola bars that require urgent attention, 2 types that will require attention next, and 2 more types after that.

Pro Tip #1 – Generate Expiry Summary

The counting of the highlighted product types was done manually. With large datasets, that is obviously not the efficient way to go. Suppose you want to create a summary of the highlighted data, you can use the COUNTIF function to count the cells.

The COUNTIF function counts the cells in a range that fulfil the condition specified in the formula. For supplying more conditions, you can use the COUNTIFS function instead.

Have a look at the summary we have created below:

Generate Expiry Summary

The COUNTIFS function has done the effort of counting the cells for us. Below are the formulas we have used for counting the cells:

Product types expiring in:

=COUNTIFS(D5:D20,">="&TODAY(),D5:D20,"<="&TODAY()+30) //In 30 days
=COUNTIFS(D5:D20,">="&TODAY()+31,D5:D20,"<="&TODAY()+60) //Between 31 to 60 days
=COUNTIFS(D5:D20,">="&TODAY()+61,D5:D20,"<="&TODAY()+90) //Between 61 to 90 days

As done in the formulas specified in the rules, the same date ranges have been applied in these formulas. The range (i.e. D5:D20 in our case example) is given before each condition and each formula has 2 conditions. First is the lower limit of the date range and the second is the upper limit. In the first formula, the lower limit is greater than equal to today and the upper limit is less than equal to 30 days after today.

The current date is added using the TODAY function. The logical operators (<, >, =) need to be enclosed in double quotes in the formula, followed by an ampersand (&) to connect it to the TODAY function.

The second formula has 31 days as the lower limit and 60 days as the upper limit. The third formula is nearly the same as the second one just with the limits furthered to 61 and 90 days.

Note: The cells have been manually highlighted in the summary.

Pro Tip #2 – Calculating Days to Expiry

Creating a column with the number of days to expiry would be another helpful detail for analyzing expiry dates because isn't it easier to coast through numbers instead of dates? Added to our original dataset, below you can see the results along with the formula to calculate the number of days to expiry:

=D5-TODAY()

Subtract the date in column D from the current date (using the TODAY function) which will give the difference between the two.

Generate Expiry Summary

The positive numbers denote the number of days to expiry. The negative numbers mean that the date in column D was earlier than the current date.

Note: If, while applying this formula, the results are in a date format, change the format of the cells to General (from the Number bar in the Home tab). The outcome is formatted as a date because the calculation includes a date. Excel is trying to be helpful here, but the regular number is what we need.

Can we gather that you can highlight expiry dates in Excel now? We certainly hope so. We gave you our take on identifying expiry dates and some tips and tricks around it that would help in evaluating the situation regarding expiration. While you're engaged with the Excel highlighters, we'll piece together another helpful how-to to answer another Excel situation.

About Mehwish Javaid

Mehwish, an ACCA-qualified professional, transitioned from an audit trainee to an Excel specialist. With a foundation in financial auditing, her 4+ years of Excel expertise, showcased as a Content Specialist at ExcelTrick, bridges her auditing background with advanced spreadsheet skills. Read more...