Function Problems

How to Find the Second-Most-Recent Date in Excel Using Formulas

Rana Garcia Oct 21, 2026 868 views

Question details

The user needs a formula to extract the second-most-recent order date associated with a specific customer code from a dataset containing multiple dates.

How to Find the Second-Most-Recent Date in Excel Using Formulas

Product: Excel
Device & OS: not provided
Scenario: Data analysis requiring the conditional extraction of the second latest date (e.g., previous order date) per customer.
Observed behavior: Requires returning the second maximum date value conditionally based on customer codes.

Before you start

Ensure your date column is formatted as actual dates in Excel, not text, and identify the cell references for your customer codes and order dates.

Solution 1Recommended

Use the MAXIFS Function to Find the Second-Most-Recent Date

The most efficient way to find the second latest date conditionally is by combining MAXIFS with a criteria that excludes the absolute latest date.

The MAXIFS function allows you to find the maximum value based on one or multiple criteria. By setting a condition to look for dates strictly less than the most recent date, it automatically identifies the second-most-recent date.

Calculate the most recent date

First, ensure you have the most recent date calculated for the customer (e.g., in cell D2) using a standard MAXIFS formula like `=MAXIFS(Sheet1!$B$2:$B$1000, Sheet1!$A$2:$A$1000, A2)`.

Enter the second-most-recent date formula

In a new cell, enter the formula `=MAXIFS(Sheet1!$B$2:$B$1000, Sheet1!$A$2:$A$1000, A2, Sheet1!$B$2:$B$1000, "<"&D2)`.

Apply and format the result

Press Enter to apply the formula, which will return the second-most-recent date. Format the result cell as a Date if it appears as a raw number.

Fill the formula down

Click and drag the fill handle (the small square at the bottom-right corner of the cell) down to apply the formula to the remaining cells for other customers.

Use the MAXIFS Function to Find the Second-Most-Recent Date

Date Formatting: If the formula returns a numeric value like 44636, simply right-click the cell, select Format Cells, and choose Short Date to display it correctly.

Solution 2

Use LARGE and IF Functions (For Older Versions of Excel)

If your version of Excel does not support MAXIFS, you can use an array formula combining LARGE and IF.

Powerful Spreadsheet Tool

Easily Manage Complex Formulas in WPS Spreadsheet

WPS Spreadsheet fully supports advanced logical and statistical functions, including MAXIFS, LARGE, and array formulas. You can perform complex data analysis with the same familiar interface as Microsoft Excel.

1. Open your dataset: Launch WPS Spreadsheet and open your document containing the customer orders.
2. Enter the formula: Select the target cell and type `=MAXIFS(`.
3. Select ranges and criteria: Select your date range, customer range, customer criteria, and add the `<` condition referencing the most recent date.
4. Apply calculation: Press Enter and drag the fill handle to complete your calculations instantly.

100% compatible with Microsoft Excel (.xlsx) formats and formulasFree built-in advanced functions like MAXIFS, MINIFS, and XLOOKUPLightweight application with fast processing for large datasetsFamiliar user interface with no learning curve

Frequently Asked Questions

Why does my MAXIFS formula return a 5-digit number instead of a date?

Dates in spreadsheets are stored as serial numbers. To fix this, right-click the cell, select Format Cells, go to the Number tab, and choose Date.

How can I find the third-most-recent date?

You can adjust the MAXIFS logic to look for dates less than the newly found second-most-recent date, or use the LARGE function with an array formula like `=LARGE(IF(range=criteria, dates), 3)`.

Does the MAXIFS function work across different sheets?

Yes, you can reference data on another sheet by including the sheet name followed by an exclamation mark before the range, such as `Sheet1!$B$2:$B$1000`.

Excel & Data Problems