Find Earliest or Latest Date Based on Criteria – Excel & Google Sheets

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on February 8, 2023
Download Example Workbook

Download the example workbook

This tutorial will demonstrate how to use the MAXIFS and MINIFS Functions to identify the earliest and last dates that meet certain conditions in Excel and Google Sheets.

mf find earliest latest date with criteria

 

Find Latest Date with MAXIFS Function

First, we will show how to use the MAXIFS Function to identify the last relevant date from a cell range.

The MAXIFS Function outputs the largest value in a range that meets a specified criteria.

This example will show the latest Sales Date for each type of Product:

=MAXIFS(B3:B8,C3:C8,E3)

MAXIFS

The MAXIFS Function follows the same syntax style as the SUMIFS and AVERAGEIFS Functions..

To find the latest Sales Date for each Product that is before the date 12/31/2020, we can add a second criteria by using the DATE Function to define a date criteria:

=MAXIFS(B3:B8,C3:C8,E3,B3:B8,"<"&DATE(2020,12,31))

MAXIFS 2 conditions

Finding Earliest Dates with MINIFS Function

In a similar way to the example above, we can use the MINIFS Function to identify the earliest relevant date from a cell range.

The MINIFS Function outputs the smallest value in a range that meets a specified criteria.

This example will show the earliest Sales Date for each type of Product:

=MINIFS(B3:B8,C3:C8,E3)

MINIFS

Locking Cell References

To make our formulas easier to read, we’ve shown the formulas without locked cell references:

=MINIFS(B3:B8,C3:C8,E3)

But these formulas will not work properly when copy and pasted elsewhere in your file. Instead, you should use locked cell references like this:

=MINIFS($B$3:$B$8,$C$3:$C$8,E3)

Read our article on Locking Cell References to learn more.

Alternative Method Using MAX and IF Functions

The MAXIFS and MINIFS Functions were added to Excel from 2007 onwards. Before this new release, you would need to use the MAX (or MIN) and IF Functions to produce the same result.

To show how this can be done, we can replicate the example to show the latest Sales Date for each type of Product using the MAX and IF Functions in an array formula:

{=MAX(IF(C3:C8=E3,B3:B8))}

MAX IF

As this example uses an array formula, it needs to be entered by pressing CTRL+SHIFT+ENTER. Doing this automatically shows { } array brackets around the formula. These do not need to be typed manually.

Find Earliest or Latest Date Based on Criteria in Google Sheets

These formulas work exactly the same in Google Sheets as in Excel.

find earliest latest date with criteria gsheets

AI Formula Generator

Try for Free

Excel Practice Worksheet

practice excel worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!

Free Download

Return to Excel Formulas List