How to find the earliest or latest date base on criteria in Excel?
When working with large data sets in Excel, it is common to encounter scenarios where you need to find the earliest or latest date associated with a specific criterion—such as a product, category, or person. For example, you may have a table where Column A contains product names and Column B contains dates, and you want to quickly determine the earliest or most recent date for a particular product, as demonstrated in the screenshot below. This task can be quite challenging to do manually, especially as the amount of data increases or as criteria become more complex. Therefore, efficiently extracting such information can save significant time and help in making timely decisions based on your data.
Find the earliest / latest date based on criteria with formulas
Find the earliest / latest date based on criteria with Kutools for Excel
Use a Pivot Table to get earliest / latest dates by criteria
Find the earliest / latest date based on criteria with formulas
Excel supports array formulas and dynamic functions that allow you to directly retrieve the oldest or latest date that meets a given condition. This method is practical for small to medium datasets and provides results in real time. Keep in mind that array formulas require correct input and formatting, and are most suitable when criteria are clear and datasets are not excessively large.
To get the earliest date based on criteria:
1. Enter the following formula into a blank cell (for example, cell D2):
=MIN(IF($A$2:$A$16="Office Tab",$B$2:$B$16))After typing the formula, do not simply press Enter. Instead, press Ctrl + Shift + Enter to make it an array formula. If successful, curly braces will appear around the formula in the formula bar. The result may initially appear as a 5-digit serial number.
Tips: In this formula:
- $A$2:$A$16 — This is the range containing your criteria (e.g., product names).
- "Office Tab" — Replace this with your own condition or reference a cell holding your criterion.
- $B$2:$B$16 — This is the date range from which the result is returned.
2. To display the result as a readable date instead of a serial number, select the result cell, go to the Home tab, click the Number Format dropdown (generally showing 'General'), and select Short Date. Now, the earliest date matching your criteria will be shown in date format as expected.
To get the latest date based on criteria:
To find the latest date for the same criteria, use the following array formula, entering it and pressing Ctrl + Shift + Enter as before:
=MAX(IF($A$2:$A$16="Office Tab",$B$2:$B$16))Practical tip:
For newer versions of Excel (Microsoft365 and Excel2021+), you can sometimes use the more dynamic MINIFS and MAXIFS functions to achieve similar results without array formulas:
=MINIFS($B$2:$B$16, $A$2:$A$16, "Office Tab")=MAXIFS($B$2:$B$16, $A$2:$A$16, "Office Tab")MINIFS and MAXIFS are easier to use and require just pressing Enter after typing the formula. If your version supports these, they are safer and more efficient for large ranges.
Find the earliest / latest date based on criteria with Kutools for Excel
Kutools for Excel’s Advanced Combine Rows feature can help you obtain the earliest or latest date for each item in key column seamlessly, without writing or understanding any formulas. This method is particularly beneficial when you have multiple criteria to process and prefer an all-in-one, graphical user interface.
If you have installed Kutools for Excel, proceed with the following steps:
1. To prevent altering your original data, copy and paste the data into a new range. Select the range you wish to use for the combination process.
2. Click Kutools > Merge & Split > Advanced Combine Rows.
3. In the Combine Rows Based on Column dialog box, click the column name you want to use as the criteria (for example, "Product"), then select the Primary Key option. This sets your criteria for combining rows.
4. Next, click the column containing dates, then select Calculate and choose either Max or Min depending on whether you want to find the latest or earliest date. Selecting Min returns the oldest date, and Max gives you the most recent date for each group.
5. Click OK to generate the summarized table. The earliest or latest date for each item will be displayed as shown below:
 |  |  |
Tips:
- If you check My data has headers, headers will be correctly identified and not included in calculations.
- For best results and to ensure the output is in date format, uncheck the Use formatted values option.
- This feature is suitable for quickly summarizing datasets by different categories without needing to set up formulas or pivot tables.
Notes & Troubleshooting:
- Double-check that your data ranges are accurate and include no merged or hidden cells to avoid calculation errors.
- If you need to process data based on multiple criteria or include more complex summary calculations (such as more than just earliest/latest dates), Kutools offers flexible options but always review the combination settings before confirming.
Use a Pivot Table to get earliest / latest dates by criteria
Pivot Tables are one of Excel’s most versatile data analysis features. They allow you to group data by criteria and summarize values using functions such as Min (for earliest date) or Max (for latest date). This is a highly visual approach suitable for all users, especially when you need interactive, summary-based results and wish to avoid complex formulas or scripts.
Usage scenario: Ideal for summarizing large tables by groups (such as product, person, or project) and instantly seeing the earliest or latest date for each. Not suitable if granular, cell-by-cell calculations or advanced conditional logic are required.
Please do with the following steps one by one:
- Select any cell in your dataset (ensure your data includes headers).
- Go to the Insert tab and click PivotTable. In the pop-up dialog, confirm the range and select where to place the pivot table (existing sheet or new sheet).
- Drag the column with your criteria (e.g., "Product") into the Rows area.
- Drag the date column into the Values area. By default, it may summarize as Count or Sum.
- To change the calculation, click the drop-down arrow on the date field in the Values area, select Value Field Settings, and choose either Min (for earliest date) or Max (for latest date). Click OK.
Result: The pivot table will now display each group in your criteria column alongside the corresponding earliest or latest date from the date column.
- You can add the date field twice to the Values area—one set as Min and one as Max—so both earliest and latest dates are shown for each group.
- If the output dates do not look correct, right-click the results and choose Format Cells > Date to adjust the display format.
- Whenever your source data changes, simply right-click the Pivot Table and select Refresh to instantly update all summaries with the latest figures.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in