How to Find Minimum and Maximum Dates in Excel Using MINIFS and MAXIFS
Question details
The user needs to extract the earliest and latest transaction dates for a specific order entry type without relying solely on XLOOKUP.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Analyzing production orders or transaction logs to determine the exact start and end dates for a specific category, such as "Consumption".
- Observed behavior
- The goal is to return the exact earliest (minimum) and latest (maximum) posting dates that match the specified entry type criterion.
Ensure that the column containing your actual posting dates is properly formatted as Date values and not as Text, otherwise the minimum and maximum calculations will return incorrect results.
Use MINIFS and MAXIFS Functions
Using MINIFS and MAXIFS is the most direct alternative to XLOOKUP for extracting boundary dates based on specific conditions.
While XLOOKUP is powerful, it typically returns the first or last matching value based on search direction. If your dataset is not perfectly sorted by date, XLOOKUP might return an incorrect earliest or latest date. MINIFS and MAXIFS scan the entire dataset and accurately extract the true minimum and maximum dates that meet your criteria regardless of the sort order.
Select the cell where you want the earliest date to appear. Type the formula `=MINIFS(DataTable[Actual Posting], DataTable[Item Ledger Entry Type], "Consumption")` to evaluate the minimum date based on your specific criteria.
In the adjacent cell for the latest date, input the formula `=MAXIFS(DataTable[Actual Posting], DataTable[Item Ledger Entry Type], "Consumption")` and press Enter.
Select both formula result cells. Navigate to the Home tab, click the Number Format dropdown, and choose 'Short Date' so the numerical output is displayed as a readable calendar date.
Easily Calculate Conditional Dates with WPS Spreadsheet
WPS Spreadsheet fully supports advanced array and conditional functions like MINIFS, MAXIFS, and XLOOKUP, allowing you to seamlessly analyze transaction dates and handle complex datasets with ease.
- 1. Open your dataset in WPS Office: Launch WPS Office and open your spreadsheet containing the transaction and posting date data.
- 2. Apply the conditional formulas: Type the =MINIFS() or =MAXIFS() formulas directly into your target cells, selecting your date ranges and typing your criteria.
- 3. Format as Date: Go to the Home tab, click the Number Format dropdown, and select Short Date to view your results correctly.
Frequently Asked Questions
Can I use XLOOKUP to find the minimum or maximum date?
Yes, but only if your data is explicitly sorted by date. By sorting dates from oldest to newest, you can use XLOOKUP with a 'Search first-to-last' mode for the minimum date, and 'Search last-to-first' mode for the maximum date. However, using MINIFS and MAXIFS is safer because they do not require the data to be sorted.
Why is my MINIFS formula returning 0 or 1/0/1900?
This happens when the formula cannot find any rows matching your criteria, so it returns 0. In Excel's date system, the number 0 is displayed as January 0, 1900. Double-check your criteria spelling and ensure there are no hidden spaces in your data.
What if my version of Excel does not support MINIFS and MAXIFS?
MINIFS and MAXIFS are available in Office 2019 and newer. If you are using an older version, you can use an array formula. For the minimum date, use =MIN(IF(CriteriaRange="Consumption", DateRange)) and press Ctrl+Shift+Enter.
