'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Find the Maximum Date in Excel While Ignoring Blank Cells
Question details
The user needs to accurately calculate the maximum (latest) date in a range while ignoring blank cells to prevent formula errors.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating the latest date in a dataset containing empty date cells using formulas or pivot tables.
- Observed behavior
- Standard MAXIFS formulas may return incorrect far-future dates (e.g., 9999) or evaluate blanks as zeros (1900 dates) when blank cells are included in the range.
Ensure your target column contains valid Excel dates formatted as 'Date' rather than 'Text', as formulas rely on underlying numerical serial values to determine the maximum date.
Use an Array Formula with MAX, IF, and ISBLANK
Combine the MAX, IF, and ISBLANK functions to evaluate the date range safely, treating blank cells as zeros before finding the highest value.
This method converts all blank cells to zero inside the formula's memory. Since valid dates are large numbers (e.g., 40000+), the zeros are ignored when the MAX function retrieves the highest value.
Click on the empty cell where you want the latest date to be displayed.
Type the formula =MAX(IF(ISBLANK(A2:A100),0,A2:A100)), making sure to replace 'A2:A100' with your actual data range.
If you are using an older version of Excel (prior to Excel 365), press Ctrl+Shift+Enter to confirm it as an array formula. The formula will be wrapped in curly brackets {}.
Right-click the result cell, select 'Format Cells', choose 'Date' under the Number tab, and click OK.
Use MAXIFS with a Non-Blank Condition
Filter out empty cells directly within the MAXIFS formula by adding criteria that exclude blanks.
Extract the Maximum Date Using a PivotTable
Create a PivotTable to automatically group your data and extract the maximum date without writing any formulas.
Easily Manage and Analyze Dates with WPS Spreadsheet
WPS Spreadsheet fully supports Excel array formulas, MAXIFS, and PivotTables natively. You can effortlessly handle complex date calculations, ignore blanks, and manipulate large datasets in a lightweight, user-friendly interface.
- 1. Install WPS Office: Download and install WPS Office for free from the official website.
- 2. Open Your Data: Launch WPS Spreadsheet and open your existing dataset containing the date column.
- 3. Apply the Formula: Enter the formula =MAX(IF(ISBLANK(range),0,range)) and press Ctrl+Shift+Enter to instantly get the latest date while bypassing blank cells.
Frequently Asked Questions
Why does my MAX function return January 0, 1900?
This happens when your formula calculates a blank cell or a zero value as the maximum. In Excel's default date system, the number 0 corresponds to the date January 0, 1900. You can fix this by using the MAXIFS or array formula solutions above to ignore zeros.
Can I use the MAX function on dates formatted as text?
No, the MAX function only works on numeric values, and Excel treats text dates as strings without numeric weight. You must convert the text to actual date values using the DATEVALUE function or the 'Text to Columns' tool before applying your MAX formula.
How do I find the maximum date based on specific criteria?
You can use the MAXIFS function for this. The syntax is =MAXIFS(Max_Range, Criteria_Range1, Criteria1). This allows you to find the latest date corresponding to a specific ID, name, or status while naturally supporting multiple conditions.
