How to Find the Earliest Date Based on a Condition in Excel
Question details
The user needs to extract the earliest non-blank date from a specified range on one worksheet based on a condition met in another column, and display the result on a different worksheet.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Filtering a large dataset to find the minimum date matching specific criteria, while ignoring blank cells to prevent incorrect minimum value evaluations.
- Observed behavior
- When the MIN function evaluates a range containing blank cells or no qualifying values, it incorrectly returns a zero date, typically displayed as 00/01/1900.
Verify that your source data contains properly formatted Excel dates rather than text strings, and check if your version of Excel supports Dynamic Array formulas like FILTER.
Use MIN and FILTER (For Microsoft 365 and Excel 2021+)
This is the most efficient method for modern Excel versions, using the FILTER function to exclude blanks and apply conditions before finding the minimum date.
The FILTER function creates an array of dates that strictly meet your criteria (e.g., column M equals 1 and the date range is not blank). The MIN function then wraps around this filtered array to return the earliest valid date.
Click on the cell in the worksheet where you want the earliest date to be displayed.
Type the formula: =MIN(FILTER(Sheet1!G6:L603,(Sheet1!M6:M603=1)*(Sheet1!G6:L603<>""))) and press Enter.
Right-click the result cell, select 'Format Cells', navigate to the 'Number' tab, choose 'Date', and select your preferred date format.

Use a Nested IF Array Formula (For Older Excel Versions)
Use this traditional array formula if the FILTER function is unavailable in your version of Excel.
Calculate Conditional Dates Seamlessly in WPS Spreadsheet
WPS Office Spreadsheet provides full support for advanced array formulas, MIN, IF, and dynamic data filtering. You can easily calculate conditional minimum dates without worrying about version compatibility while enjoying a fast, lightweight interface.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your date datasets.
- 2. Input the conditional formula: Select your target cell and input the nested IF or MINIFS formula exactly as you would in Excel.
- 3. Calculate and format: Press Ctrl+Shift+Enter if using a traditional array formula. Right-click the cell to quickly format it as a valid date.

Frequently Asked Questions
Why does my MIN formula return 00/01/1900?
In Excel, a blank cell evaluates to a numerical zero. Since dates are stored as serial numbers starting from January 1, 1900, a zero value is displayed as 00/01/1900. You must explicitly exclude blank cells (e.g., using <>"") in your formula to avoid this.
Can I use the MINIFS function instead of an array formula?
MINIFS is great for single-column date ranges. However, if your date range spans multiple columns (like G6:L603) while the criteria range is a single column (M6:M603), MINIFS will return a #VALUE! error because the sizes of the ranges do not match. In that case, FILTER or an IF array is required.
How do I handle scenarios where no dates match the condition?
When no matches are found, MIN might return an error or zero. You can wrap your entire formula in the IFERROR function, or use an IF statement to check if the result is 0 and output a custom message like "No Match" instead.
Why does my formula show curly braces {} around it?
Curly braces indicate that the formula was entered as a legacy Array Formula using Ctrl+Shift+Enter. You cannot type these braces manually; Excel adds them automatically to process calculations over arrays instead of single cells.




