Return earliest date for a specific year in a range

This tutorial shows how to get the earliest date for a specific year in a range using an array Excel formula, with the MIN, IF and YEAR functions

EXCEL FORMULA 1. Return earliest date for a specific year in a range

EXCEL

Hard coded formula
Return earliest date for a specific year in a range
Cell reference formula
Return earliest date for a specific year in a range
{=MIN(IF(YEAR(B7:B11)=2019,B7:B11))}
{=MIN(IF(YEAR(B7:B11)=C4,B7:B11))}
GENERIC FORMULA

{=MIN(IF(YEAR(range)=year,range))}

ARGUMENTS
range: The range of dates from which you want to extract the earliest date by the specific year.
year: The year for which you want to extract the earliest date from the selected range.

GENERIC FORMULA

{=MIN(IF(YEAR(range)=year,range))}

ARGUMENTS
range: The range of dates from which you want to extract the earliest date by the specific year.
year: The year for which you want to extract the earliest date from the selected range.

EXPLANATION

This is an array formula that uses the MIN, IF and YEAR functions to return the earliest date for a specific year from a selected range.
The YEAR function is used to return the year of the dates from the selected range. The YEAR function is combined with the IF function to test if the year of the dates meet the specific year that has been nominated. In this example the year that has been nominated is 2019. For the dates that meet the criteria, the IF function will return a TRUE value. The MIN function then finds the smallest number, which in this case will be the earliest date, from the range that the IF function has identified as TRUE, meaning it includes only those dates that contain the nominated year.

Click on either the Hard Coded or Cell Reference button to view the formula that has the year directly entered into the formula or referenced to a specific cell.

RELATED TOPICS

Related Topic Description Related Topic and Description
Return latest date in a range How to get the latest date in a range
Return earliest date in a range How to get the earliest date in a range
Return latest date for a specific year in a range How to get the latest date for a specific year in a range

RELATED FUNCTIONS

Related Functions Description Related Functions and Description
YEAR Function The Excel YEAR function returns the year from a specified date
MIN Function The Excel MIN function returns the smallest value from a specified range of numeric values
IF Function The Excel IF function performs a test on specified conditions entered into the formula and returns a specified value if the result is TRUE or another specified value if the result is FALSE