How to Identify or Highlight Expired or Upcoming Dates in Excel?

Identifying the expired or upcoming dates can be useful for cases such as understanding the project’s last date, the last date for some important activities, and many others. In the food and medicine industry expiration dates are important as companies need to dispose of their products according to the date of expiry as the food and medicine industry must follow some standards to deliver healthy products to living beings. In this article will learn the process of analyzing the data such as identifying or highlighting the expired or upcoming dates to Excel. This article describes two examples, first example focuses on the use of conditional formatting features, while the second example is based on calculating the count of the expired or passed away dates. To understand all the steps, refer to the below-provided explanation.

Example 1: To identify the expired number of dates present in the data sets, by using the conditional formatting feature

Step 1

In this article, the user will learn the process to highlight expired or upcoming dates. Before starting the example, as usual, assume some data. Sample data provided for this example is provided below:

Step 2

After that go to the “Home” tab, and then click on the “Conditional Formatting” option. Further, select the “New Rule” option. A snapshot for the same is provided below:

Step 3

The above step will open a “New Formatting Rule” dialog box. This box, contains different options, in the “Select a Rule Type” label, select the last “Use a formula to determine which cells to Format” option. A snapshot for reference is provided below:

Step 4

In the next step go to the input label provided for the “format values to determine which cells to format” and type formula “=B3<=today()”. After that click on the “format” button. snapshot for reference is provided below:

Stepwise explanation for formula:=B3<=today()

• "TODAY()": This function will return the current or today’s date.

• "=B3": This cell will refer to the value stored in cell B3. Consider that cell contains a date, for example, 2023-07-15.

• "=B3<=TODAY()": This expression compare the value in cell B3 with the current date, and return all the expired or passed dates.

Step 5

The above step will open a “Format Cells” dialog box. In the opened box, click on the “Fill” option, and select any required color. Among the list of available colors, select the “blue” color, and click on the “OK” button. A snapshot for the same is provided below:

Step 6

After applying the data all the data gets highlighted as the current date is 20-Jul-2023, and all the provided dates are less than the current date. Please note that the result will be generated according to the input sample data. By changing the data user will always obtain a new output. Sample output for this example is guided below:

Example 2: To identify the expired number of dates present in the provided data sets.

Step 1

Assume a set of date values taken to demonstrate the use of data. The format used for the provided dates are “dd-mmm-yy”. Sample data is depicted below for user reference:

Step 2

In the next step will go to the D3 cell, and type formula “=TODAY()”. This formula will display the current date on the excel sheet. Snapshot for reference is provided below:

Step 3

The above step will display the data in the “dd-mm-yy” format. Snapshot for generated value is provided below:

Step 4

After that select all the available data rows, go to the “kutools” tab, and click on the “editing” option. Further, click on the “Select” option, and choose the option “Select Specific Cells”. A snapshot for reference is provided below:

Step 5

The above step will open a “Select Specific Cells” dialog box. In the first add the data range manually, that is add values “B3:B8”. After that go to the selection type input label, and then click on the “Cell” radio button. Further, go to the specific type label, click on the “Less than” option, enter today’s date “16-07-23” manually, and click on the “OK” button. A sample snapshot is listed below for reference:

Step 6

As soon as the user clicks on the OK button, an input dialog box, with the message 5 cells found and selected will be displayed. Click on the “OK” button.

Please note that with the help of kutools, the user will be only able to calculate the total number, and no other processing such as highlighting data values is possible here. Although, the same can be achieved by using the user-defined formula, already explained in the previous example.

Conclusion

After the successful completion of this article, the user will be able to highlight and identify the available expired or upcoming dates in Excel. This article describes two methods to perform this task such as using the user-defined formula, and second, using kutool to perform some tasks.