'%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 Return the Latest Date Based on Criteria in Excel
Question details
The user needs an Excel formula to evaluate a range of dates and return the most recent one that matches a specific condition (like a tank designation), leaving the cell blank instead of showing zero if no matching date exists.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking inventory, maintenance, or refill logs where the most recent event date needs to be extracted based on a specific item's identifier.
- Observed behavior
- Without the correct logical wrapping, returning a maximum date based on criteria can result in a "0" (which formats to a 1900 date) when no matches are found in the dataset.
Ensure that your source date column is formatted as standard Excel dates (not text) and that your criteria column contains exact matches for the values you are looking up.
Use the MAXIFS Function Nested Inside an IF Statement
This is the most efficient method to find the maximum value (the latest date) based on a specific condition while handling zero values cleanly.
The MAXIFS function is designed to return the maximum numeric value among cells specified by a given set of conditions. Because dates are stored as serial numbers in Excel, MAXIFS easily finds the latest date.
By wrapping the MAXIFS function in an IF statement, you can check if the result equals 0 (which happens when no matches are found). If it is 0, the formula returns a blank space (""), preventing Excel from displaying an incorrect default date like 1/0/1900.
Click on the cell where you want the latest date to be displayed (for example, cell J49).
Type the formula =IF(MAXIFS($A$5:$A$41,$B$5:$B$41,C49)=0,"",MAXIFS($A$5:$A$41,$B$5:$B$41,C49)) into the formula bar and press Enter.
In this formula, $A$5:$A$41 represents the fixed range containing your dates, $B$5:$B$41 is the fixed range containing your lookup criteria (e.g., tank designations), and C49 is the specific lookup value.
Click and drag the fill handle at the bottom-right corner of the selected cell to apply the formula down through the remaining cells (e.g., J49 through J52). The absolute references ($) will keep your source ranges locked.
Easily Handle Complex Formulas with WPS Spreadsheet
WPS Spreadsheet provides robust support for advanced data analysis and logical functions, including MAXIFS, IF, and array formulas. You can effortlessly manage tracking logs and conditional date calculations with high performance.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the inventory or tracking data.
- 2. Use the Insert Function tool: Navigate to the 'Formulas' tab and click 'Insert Function' to easily search for and build your MAXIFS formula using the guided dialog box.
- 3. Format your results: Highlight your formula result cells, right-click to select 'Format Cells', and apply your preferred Date format.
Frequently Asked Questions
Why does my MAXIFS formula return a date in the year 1900?
In Excel, dates are stored as serial numbers starting from January 1, 1900 (serial number 1). If MAXIFS finds no matches for your criteria, it returns a 0. When formatted as a date, 0 displays as January 0, 1900. Wrapping your MAXIFS in an IF statement to return "" (blank) when the result is 0 resolves this issue.
Can I use the MAXIFS formula if my dates are formatted as text?
No, the MAXIFS function only evaluates numeric values, and true dates are stored as serial numbers. If your dates are stored as text, MAXIFS will return 0. You need to convert the text to actual dates using the DATEVALUE tool or 'Text to Columns' feature before using the formula.
What if I am using an older version of Excel that does not support MAXIFS?
The MAXIFS function was introduced in Excel 2019 and Microsoft 365. If you are using an older version, you can achieve the same result using an array formula: =MAX(IF($B$5:$B$41=C49, $A$5:$A$41)). You must press Ctrl + Shift + Enter to execute it as an array formula.
