Function Problems

How to Extract a Date from a Row Based on the Year in Excel

Khadija Khan Oct 9, 2026 869 views

Question details

The user needs to extract a specific date from a horizontal range (row) if it matches a certain year (e.g., 2024) and return a blank value if there is no match.

How to Extract a Date from a Row Based on the Year in Excel

Product: Excel
Device & OS: not provided
Scenario: Filtering and extracting specific dates from a dataset based on the year.
Observed behavior: Returns the matching date from the row if it falls within the specified year, or displays a blank cell if no date in that year is found.

Before you start

Ensure your dataset contains properly formatted date values and identify the row range (e.g., A2:Z2) you want to extract the date from before applying the formula.

Solution 1Recommended

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

This method uses the dynamic array FILTER function combined with MIN to cleanly extract the date and IFERROR to handle blanks.

This is the most efficient and modern approach for extracting conditionally matched dates from an array. It requires a version of Excel that supports dynamic array functions.

Select the destination cell

Click on the empty cell where you want the extracted date to appear.

Enter the FILTER formula

Type the formula =IFERROR(MIN(FILTER(A2:Z2,YEAR(A2:Z2)=2024)),""), replacing A2:Z2 with your actual row range and 2024 with your target year.

Apply the formula

Press Enter to execute the calculation.

Format as Date

Right-click the result cell, select 'Format Cells', navigate to the Number tab, and choose 'Date' to ensure the result displays correctly.

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

Tip for multiple dates: Using the MIN function ensures that if there are multiple dates from 2024 in the row, the earliest date will be extracted.

Solution 2

Use an Array Formula (Excel 2019 and Older)

For older versions of Excel that do not support dynamic arrays, you can use a combination of IF, MIN, and YEAR functions.

WPS Spreadsheet Solution

Extract Dates Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array formulas and functions like FILTER, MIN, and IF. You can seamlessly apply date extraction formulas to your datasets with high performance.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your workbook containing the dates.
2. Select the target cell: Click on an empty cell where you want to extract the date.
3. Enter the formula: Input =IFERROR(MIN(FILTER(A2:Z2,YEAR(A2:Z2)=2024)),"") and press Enter.
4. Format the output: Press Ctrl+1 to open the Format Cells dialog and select Date.

100% compatible with Microsoft Excel formulas and file formats (.xlsx)Flawlessly supports dynamic arrays and advanced date extraction functionsFree to download, lightweight, and features a familiar tabbed interface

microsoft office alternative - wps office

Frequently Asked Questions

Why is my extracted date showing as a 5-digit number?

Spreadsheet software stores dates as sequential serial numbers. A 5-digit number like 45300 means the formula worked successfully, but the cell format is currently set to General. Right-click the cell, select Format Cells, and choose Date.

How can I extract a date based on a month instead of a year?

You can modify the formula by replacing the YEAR function with the MONTH function. For example, use =IFERROR(MIN(FILTER(A2:Z2,MONTH(A2:Z2)=5)),"") to extract a date that falls in May.

Can I reference a cell for the year instead of typing it directly into the formula?

Yes, you can replace the hardcoded year with a cell reference. For example, if cell B1 contains your target year (2024), update the formula to =IFERROR(MIN(FILTER(A2:Z2,YEAR(A2:Z2)=B1)),"").

Excel & Data Problems