Get Max Date from a List of Dates in Excel (Formula)

puneet-gogia-excel-champs

- Written by Puneet

In Excel, you can use a simple formula if you have a list of dates and want to get the date that is the max out of all those. For this, all you need to do is to use the MAX function, and if you want to get the max date using a condition then you need to use the MAXIFS.

This tutorial will help you to understand both of the functions to get the max date.

get-max-date-from-list-of-dates

Formula to Get the Max Date

  1. First, in a cell, enter MAX function.
  2. Now, enter the closing parentheses to start the function.
  3. Next, refer to the range where you have the list of dates.
  4. In the end, enter the closing parentheses and the hit enter to get the result.
formula-to-get-max-date

Once you hit enter it returns the max date from the data. And in our example, 10-Nov-2023 is the max date.

returns-max-date-from-data

Get the Max Condition with a Condition

Now if you want to get the max date from the same data but by using a condition, you need to use the MAXIFS function.

=MAXIFS(A1:E7,A1:E7,">"&DATE(2023,2,1),A1:E7,"<"&DATE(2023,3,31))

In the above formula, we have specified a condition to get the maximum date between 01-Feb-2023 and 31-Mar-2023. And it returns 14-Mar-23 in the result.

To understand this, you must know the MAXIFS function and its arguments.

  • max_range
  • criteria_range1
  • criteria1
  • criteria_range2
  • criteria2
get-max-condition-with-a-condition
  1. First, specify the max_range from where you want to get the max date.
  2. After that, in the criteria_range1, you need to specify the same range again where you have the dates. In criteria1, you need to specify the range for the lowest date for the date range along with the lower than operator.
  3. Next, in the criteria_range2, you need to specify the same date range again. In the criteria2, you need to specify the highest date and the greater than operator.
  4. In the end, close the function and hit enter to get the result.

Note: We have used the DATE function to specify the date in the criteria.

Get the Excel File

Last Updated: December 26, 2023