Formula Errors

How to Find the Earliest Date Based on a Condition in Excel

Huda QurayshiHuda Qurayshi Nov 3, 2026 869 views

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.

How to Find the Earliest Date Based on a Condition in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the destination cell

Click on the cell in the worksheet where you want the earliest date to be displayed.

2
Enter the dynamic array formula

Type the formula: =MIN(FILTER(Sheet1!G6:L603,(Sheet1!M6:M603=1)*(Sheet1!G6:L603<>""))) and press Enter.

3
Format the result as a Date

Right-click the result cell, select 'Format Cells', navigate to the 'Number' tab, choose 'Date', and select your preferred date format.

Use MIN and FILTER (For Microsoft 365 and Excel 2021+)
Dynamic Spilling: Because this uses a dynamic array, you do not need to press Ctrl+Shift+Enter. The formula will automatically spill and calculate the result.
Advanced Spreadsheet Formulas in WPS

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. 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your date datasets.
  2. 2. Input the conditional formula: Select your target cell and input the nested IF or MINIFS formula exactly as you would in Excel.
  3. 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.
Fully compatible with Microsoft Excel formulas and .xlsx formatsSupports advanced array formula calculations seamlesslyFree to use with a lightweight, user-friendly interfaceBuilt-in robust error handling for complex data sets
microsoft office alternative - wps office

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.