Exceljet

Quick, clean, and to the point

Filter on dates expiring soon

Excel formula: Filter on dates expiring soon
Summary 

To filter a set of data to show rows where dates are expiring soon (or have already expired) you can use the FILTER function with the SORT function. In the example shown, the formula in H5 is:

=SORT(FILTER(data,data[Expires]-date<=days),5)

where data is an Excel Table in the range B5:F16, and date (J2) and days (L2) are named ranges.

Explanation 

In this example, the goal is to filter a set of data to show rows where a specific date has expired or is expiring soon. The data to filter is in an Excel Table named data in the range B5:F16 and the dates to filter on are in the "Expires" column. In addition, the current date is in the named range date (J2) and the number of days to use when checking if a date is expiring soon is in the named range days (L2). The named ranges are for convenience only, to make the formula easier to read and write, and to make it easier to set up conditional formatting for dates already expired (highlighted in yellow).

The core logic

In the example shown, the formula in H5 is:

=SORT(FILTER(data,data[Expires]-date<=days),5)

Working from the inside out, the core logic in this formula checks the dates in the "Expires" column against date (J2):

data[Expires]-date<=days)

Because Excel dates are just large serial numbers, we simply subtract the date in date from each date in the Expires column. When the expiration date is in the past, the result is a negative number. When the Expires date is in the future, the Expires date is a positive number. In the example shown:

data[Expires]-date

returns the following array:

{-38;31;12;291;329;1;246;8;32;-5;15;33}

Each number represents days the number of days until expiration. Again, negative numbers are dates already expired. When this array is compared to days (L2=15):

={-38;31;12;291;329;1;246;8;32;-5;15;33}>=15

the result is an array of TRUE and FALSE values:

{TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE}

Each TRUE value is a date that has already expired or will expire in the next 15 days. This is the logic we use in the FILTER function.

FILTER function

The next step is to implement the logic above inside the FILTER function. To filter the dates to show those expiring within 15 days, we embed the logical expression above inside FILTER like this:

FILTER(data,data[Expires]-date<=days)

The array argument is the Excel Table data, and the include argument is the logical expression explained above. In this configuration, FILTER returns the six rows from the data table where the expires date has already occurred or will occur in the next 15 days.

SORT function

The final step in the problem is to sort the data by expiration date, so that dates already expired are listed first followed by dates expiring soon in the order they will expire. This can be done by nesting the FILTER function inside the SORT function like this:

=SORT(FILTER(data,data[Expires]-date<=days),5)

Here, array is the result returned by FILTER and sort_index is 5, since the "Expires" date is in the fifth column of the table.

Current date

In the named range date, the current date is returned with the TODAY function:

The TODAY function could be used directly in the formula, eliminating the reference to J2, but placing the date in a cell on the worksheet makes the operation of the formula more transparent. In addition, it makes it easy to check for dates expiring soon relative to a different date if desired.

Conditional formatting

In the example shown, conditional formatting is used to highlight dates that have already expired. The formula used to apply the rule is based on the AND function:

=AND($L5<>"",$L5<=date)

We check two conditions: (1) the date in L5 is not empty and (2) the date in L5 is less than or equal to (<=) date (L2), which holds the current date. This is an example of a mixed reference – the column is locked in order to highlight the entire row.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.