We have a range of dates and we want to look up the earliest and the latest date based on certain criteria like the earliest date for a showing movie, we can use MIN and MAX functions with IF function or INDEX function together to find the matched date based on some criteria. Except using formula, we can also use PivotTable to find the min or max date.
See the table below. We have a list of entertainments in recent days. And we want to know the earliest date and latest date for certain entertainments.
Find the Earliest Date Based on Criteria with Functions MIN & IF
Step 1: In E2 cell enter the formula =MIN(IF($A$2:$A$13=D2,$B$2:$B$13)).
Comment:
In above formula IF function returns the data satisfied the criteria $A$2:$A$13=D2, then MIN function returns the earliest date among the filtered dates.
Also check with MINIFS and MAXIFS, both are useful to if you could like put more than one condition.
Step 2: Press Ctrl+Shift+Enter to get the result. Verify that it returns a five digits number, so we need to convert it to date format.https://41295b62efc69e79ec772bead2899389.safeframe.googlesyndication.com/safeframe/1-0-38/html/container.html
Step 3: Click Home, in Number group, click General dropdown list and select Short Date.
Step 4: Verify that the five digits number is converted to date format properly.
Find the Latest Date Based on Criteria with Functions MAX & IF
Step 1: In E6 cell enter the formula =MAX(IF(A2:A13=D6, B2:B13)).
Step 2: Press Ctrl+Shift+Enter to get the result. Verify that it returns a five digits number, so we need to convert it to date format.
Step 3: Click Home, in Number group, click General dropdown list and select Short Date. Verify that number is converted to date format properly.
There is another way to find the latest date by functions MIN and INDEX. See method below.
Find the Latest Date Based on Criteria with Functions MIN & INDEX
Step 1: In E6 cell enter the formula =MAX(INDEX((D6=A2:A13)*B2:B13,)).
Step 2: Press Ctrl+Shift+Enter to get the result. We get the same 5 digits number in above method step#2. The we can follow previous steps#3-#4 to convert it to date format.
Ref: https://www.excelhow.net/how-to-find-the-earliest-and-latest-date-in-excel.html