Function Problems

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

Maira Mehtab Sep 22, 2026 870 views

Question details

The user needs to retrieve the second-most-recent activity date for specific employees using their employee IDs and a table of activity records.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Extracting specific historical data points, such as the previous activity or transaction date, based on unique employee identifiers.
Observed behavior: The user wants a dynamic formula to filter records by employee ID, evaluate the corresponding dates, and return exactly the second-largest date value.

Before you start

Ensure your date column is formatted as actual dates rather than text, and verify that your version of Excel supports Dynamic Arrays if you plan to use the FILTER function.

Solution 1Recommended

Use the LARGE and FILTER Functions (Microsoft 365 & Excel 2021)

This method dynamically filters dates for a specific employee and extracts the second largest (most recent) date using modern array functions.

The FILTER function creates an array of all activity dates associated with a specific employee. The LARGE function then evaluates that filtered array and pulls the second highest value, which corresponds to the second-most-recent date.

Select the destination cell

Click on the cell where you want the second-most-recent date to be displayed.

Input the formula

Type the formula `=IFERROR(LARGE(FILTER(Activity[Date], Activity[Employee ID]=A2), 2), "")`. Replace 'Activity[Date]' and 'Activity[Employee ID]' with your actual data ranges, and 'A2' with the cell containing the target employee ID.

Apply and format

Press Enter to apply the formula. Drag the fill handle down to apply it to other employees. Ensure the result cells are formatted as 'Date' via the Home tab.

Handling Missing Data: The IFERROR wrapper ensures that if an employee has fewer than two recorded activities, the cell remains blank instead of displaying a #NUM! error.

Solution 2

Use an Array Formula with LARGE and IF (Older Excel Versions)

If you are using an older version of Excel that does not support the FILTER function, you can achieve the same result using a traditional array formula.

Efficient Data Management with WPS Spreadsheet

Find Recent Dates Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array formulas, making it simple to filter and extract the second-most-recent dates from large datasets just like Microsoft Excel.

1. Open your dataset: Launch WPS Spreadsheet and open the file containing your employee and activity records.
2. Enter the formula: Click the target cell and input `=IFERROR(LARGE(IF(B$2:B$100=A2, C$2:C$100), 2), "")`.
3. Execute the array: Press Ctrl + Shift + Enter to run the array formula.
4. Format as Date: Right-click the cell, select 'Format Cells', choose the 'Number' tab, and select 'Date' to display the result properly.

Fully compatible with Microsoft Excel formulas like LARGE, IF, and IFERROR.Handle large datasets smoothly with lightweight performance.Easily format results as dates with a familiar user interface.Free to use for everyday data analysis and reporting tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my formula return a #NUM! error?

A #NUM! error typically occurs if the employee has fewer than two dates recorded in the dataset. Wrapping your formula in the IFERROR function, like `=IFERROR(LARGE(...), "")`, will hide this error and display a blank cell instead.

How can I find the third or fourth most recent date?

You can adjust the 'k' argument in the LARGE function. Change the '2' at the end of the formula to '3' for the third-most-recent date, or '4' for the fourth.

What if my formula returns a 5-digit number like 44215 instead of a date?

This means the cell is formatted as General or Number. Simply select the cell, go to the Home tab, click the Number Format dropdown, and choose Short Date or Long Date.

How do I handle duplicate dates for the same employee?

If an employee has multiple entries on the same day and you want to find the next strictly unique date, you need to use the UNIQUE function inside your formula, such as `=LARGE(UNIQUE(FILTER(DateRange, IDRange=TargetID)), 2)`.

Excel & Data Problems