Return latest date for a specific year in a range
This tutorial shows how to get the latest date for a specific year in a range using an array Excel formula, with the MAX, IF and YEAR functions
Hard coded formula
Cell reference formula
|
GENERIC FORMULA
{=MAX(IF(YEAR(range)=year,range))}
ARGUMENTS GENERIC FORMULA
{=MAX(IF(YEAR(range)=year,range))}
ARGUMENTS EXPLANATION This is an array formula that uses the MAX, IF and YEAR functions to return the latest 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 MAX function then finds the highest number, which in this case will be the latest 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 |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| YEAR Function | The Excel YEAR function returns the year from a specified date | |
| MAX Function | The Excel MAX function returns the largest 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 |