How to Return the Oldest Date When Another Column is Blank in Excel
Question details
The user needs an automated formula to find the earliest (oldest) date in one column, but strictly for rows where a corresponding second column is entirely blank.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Extracting the oldest pending task date from an action list where the completion date column has not yet been filled in.
- Observed behavior
- The user attempted to use the SUBTOTAL function, but it only worked when the completion column was manually filtered to display blank cells.
Ensure that your target date column is formatted as actual dates rather than text, as Excel needs to evaluate the numeric values to find the chronological minimum.
Use the MINIFS Function to Find the Oldest Date
The MINIFS function is the most direct and modern approach. It returns the smallest numeric value (dates are stored as numbers) from a range that meets your specific criteria, such as empty strings.
By setting the criteria to look for empty strings (""), the MINIFS formula filters out all completed items automatically, eliminating the need for manual table filters.
Click on the cell where you want the oldest pending date to be displayed.
Type the formula: =MINIFS(A:A, F:F, ""). In this example, column A contains your dates and column F is the completion status you want to check for blanks.
Press Enter. If the result displays as a 5-digit number, right-click the cell, select 'Format Cells', and change the format category to 'Date'.
Use a MIN and IF Array Formula (For Older Excel Versions)
If you are using a version of Excel older than 2019 that does not support the MINIFS function, you can achieve the same result using an array formula combination.
Effortlessly Calculate Minimum Dates with WPS Spreadsheet
WPS Spreadsheet fully supports advanced logical formulas like MINIFS, allowing you to quickly extract critical dates from complex action lists without resorting to manual filtering.
- 1. Open your file: Launch WPS Spreadsheet and open your task or action list workbook.
- 2. Select the target cell: Click the specific cell where you want to output the oldest pending date.
- 3. Apply the formula: Type =MINIFS(A:A, F:F, "") to check column A for the lowest date where column F is blank.
- 4. Format as Date: Press Enter, then use the Home tab ribbon to format the resulting serial number into a readable Date format.
Frequently Asked Questions
Why does my MINIFS formula return 0 or '1/0/1900'?
This happens if there are no blank cells in your criteria column, or if the formula evaluates empty rows far below your actual data. To fix this, restrict your formula to the exact row range of your data, for example: =MINIFS(A2:A500, F2:F500, "").
How can I find the newest date instead of the oldest?
You can use the MAXIFS function. The syntax is exactly the same as MINIFS. Simply enter =MAXIFS(A:A, F:F, "") to return the most recent date where the adjacent column is blank.
Why does my MINIFS result look like a random 5-digit number?
Excel stores dates as sequential serial numbers for calculation purposes (e.g., 44123 represents a specific date in 2020). If you see a number like this, simply select the cell, right-click, choose 'Format Cells', and apply your preferred Date format.
