To find the largest value recorded between two dates in Excel, use MAXIFS with the value range and date criteria. With dates in A2:A100, values in B2:B100, and the start and end dates in D2 and E2, use =MAXIFS(B2:B100,A2:A100,">="&D2,A2:A100,"<"&E2+1) to include every time on the end date as well as the start and end dates.
The MAXIFS formula for a date range
MAXIFS returns the largest number in a range when corresponding cells meet one or more conditions. In the example above, column A contains dates, column B contains the numbers to compare, D2 holds the first date, and E2 holds the last date. The formula tests each date in A against the boundaries and returns the greatest matching value from B.
The less-than condition at the end is deliberate. Excel dates can contain a time as well as a calendar date. A cell displaying June 10 may actually store June 10 at 3:00 p.m. Comparing it with a plain end date using a less-than-or-equal condition can exclude that record, because the end-date cell represents midnight at the start of June 10. Using a boundary just before the following day includes all times on June 10.
If your date column contains calendar dates only, with no time components, you can use an inclusive upper boundary instead. With dates in A2:A100, values in B2:B100, and the bounds in D2 and E2, use =MAXIFS(B2:B100,A2:A100,">="&D2,A2:A100,"<="&E2). This is also suitable when you specifically want the end date and time in E2 to be the exact final cutoff, rather than including the whole calendar day.
MAXIFS is available in Microsoft 365 and current Excel versions, including Excel 2019 and later. The formula works in Excel for Windows and Mac; Excel for the web also supports it. The function name and comma-separated arguments are the same across those platforms.
How Excel handles dates and times
Excel stores dates as serial numbers, with times represented as fractions of a day. That is why ordinary comparison operators such as greater than or equal to work for date criteria. A condition such as ">="&D2 joins the comparison operator to the date value in D2, creating a criterion that MAXIFS can evaluate. The date in D2 must be a real Excel date value, not merely text that looks like one.
For instance, a timestamp at 9:30 a.m. is later than midnight on the same calendar date. If the start date is June 1 and the end date is June 10, a formula using less than the day after June 10 includes records from June 1 at midnight through June 10 at 11:59:59 p.m. Excel stores time to a finer precision than a displayed second, so using the next day as an exclusive boundary is more reliable than trying to invent a final time such as 11:59 p.m.
Use the boundary that matches the question you are asking:
- For all records on the end date, use a condition less than the next day, as in the first formula.
- For date-only records, an inclusive less-than-or-equal condition on the end date is straightforward.
- For an exact timestamp cutoff, compare to the timestamp in the end-date cell with less than or equal to that value.
Format the date cells so they are easy to read, but remember that changing a cell’s number format does not convert text into a real date. Formatting controls appearance; the underlying value determines whether the formula can compare it correctly.
Set up the data and date boundaries
A simple, consistent layout makes the formula easier to check and maintain. Put one date or timestamp on each row, and put the corresponding measurement or amount in the same row of a separate column. Keep the start and end dates in two clearly labeled cells outside the data. For example, A1 could be Date, B1 could be Value, D1 could be Start date, and E1 could be End date.
- Enter dates or timestamps in A2:A100 and the numbers to compare in B2:B100.
- Enter the first date in D2 and the last date in E2. Use Excel date values, which you can enter as dates and display with a date number format.
- Place the MAXIFS formula in another cell, such as G2.
- Change D2 or E2 to recalculate the result for a different period.
Keep the date range and value range the same size. In this example, both cover rows 2 through 100. MAXIFS evaluates corresponding positions in its ranges: the date in A2 determines whether the value in B2 qualifies, the date in A3 determines whether B3 qualifies, and so on. Mismatched range dimensions can produce an error or make the formula invalid.
If you add new rows regularly, consider converting the data to an Excel Table using Insert > Table. Tables can expand as rows are added, which reduces the chance that a formula with a fixed endpoint misses newer records. You can then use structured references, but ensure that the date and value columns still refer to matching rows and that the formula’s criteria point to the correct start and end cells.
Worked example: find the highest value
Suppose column A contains dates and column B contains daily readings. The rows are June 1, 12; June 4, 19; June 10 at 8:00 a.m., 24; June 10 at 5:00 p.m., 21; and June 11, 30. If D2 is June 1 and E2 is June 10, the formula with the exclusive next-day boundary returns 24. It includes both June 10 readings, then selects the larger of the qualifying values. The June 11 reading of 30 is excluded.
This result answers a different question from finding the latest date in the period. To return the latest matching date or timestamp itself, use the date column as both the maximum range and the criteria range. With dates in A2:A100 and start and end dates in D2 and E2, use =MAXIFS(A2:A100,A2:A100,">="&D2,A2:A100,"<"&E2+1). If the date column includes times, the result is the latest qualifying timestamp; if it contains dates only, it is the last calendar date present in the interval.
It is also useful to distinguish a maximum from a total or an average. MAXIFS returns one greatest qualifying number; it does not add values together or calculate a typical value. If the question is instead how many records fall in the period, or what their average is, use a counting or averaging approach rather than interpreting the maximum as a summary of all the data.
Choose the right range and criteria
The maximum range should contain the numbers whose largest value you want. The criteria range should contain the dates that decide which of those numbers qualify. Those do not have to be the same column. For example, a sales sheet might have transaction dates in A, salesperson names in B, and sales amounts in C. A date-only maximum uses the date column as its criterion range and the sales column as its maximum range.
MAXIFS can apply more than one condition. You could require a date to be inside the period and a category to match a selected label. With dates in A2:A100, categories in B2:B100, amounts in C2:C100, start and end dates in D2 and E2, and the category to match in F2, use =MAXIFS(C2:C100,A2:A100,">="&D2,A2:A100,"<"&E2+1,B2:B100,F2). The formula returns the highest amount that meets all of the conditions, not the highest amount in either category or date range alone.
For a recurring report, use cell references for the boundaries and category rather than typing dates or labels directly into the formula. That makes the criteria visible and easier to change. It also helps avoid ambiguity from regional date formats: a date such as 4/5/2026 can be interpreted differently depending on regional settings, while a properly entered date value in a labeled cell has a clear role in the calculation.
Common problems and how to fix them
- The result is zero. MAXIFS returns 0 when it finds no qualifying cells. Check whether the start and end dates are in the expected order, whether any records fall within them, and whether the date values are real dates rather than text. A zero can also be a genuine maximum when the qualifying values are zero or negative, so verify the matching rows before treating it as an error.
- The last day appears to be missing. If the date column contains timestamps, an inclusive criterion ending at midnight may exclude later times on that date. Use the less-than-next-day condition shown above to include the entire end date.
- The formula returns an unexpected maximum. Confirm that the maximum range contains the intended measure and that every criterion range covers the same rows. Also check that the end date is not earlier than the start date and that the formula has not included totals or unrelated rows.
- Dates look correct but do not match. Some imported dates are text. A number format can make text look like a date without changing its type. Try checking a sample cell’s alignment and number format, or convert the imported column to actual dates before relying on comparisons.
- Numbers are stored as text. Text values in the maximum range are not treated as ordinary numbers by MAXIFS. Convert imported numeric text to numbers and check for currency symbols, spaces, or other characters that may have been imported as part of the content.
- The formula shows an error. Make sure the ranges have equal dimensions and that commas separate arguments in your Excel settings. Some regional configurations use semicolons as formula separators; if Excel rejects a comma version, follow the separator convention used by your installation.
A particularly easy mistake is to assume that a displayed date tells the whole story. A cell formatted to show only a date may still contain a time. If the report’s answer differs from a manual filter, inspect the underlying values and the filter’s date boundaries rather than changing the number format alone.
Alternatives and useful checks
For Microsoft 365, MAXIFS is usually the clearest choice for a maximum with date conditions. It expresses the task directly and accepts multiple criteria without requiring a special array-entry step. If the workbook must work in an older Excel release that does not support MAXIFS, a PivotTable is a practical alternative: group or filter the date field to the period, then summarize the value field by Max. The exact grouping options depend on the data and Excel version, but the result can be inspected alongside the source records.
A normal worksheet filter can also help verify the formula. Filter the date column to the same interval, then inspect the visible values or sort the value column from largest to smallest. This is a useful audit, especially when troubleshooting, but manual filtering is less convenient than a formula when the boundaries change repeatedly or the result feeds another calculation.
For a quick check, identify the rows whose dates qualify and confirm that the returned value appears among their numeric entries. If it does not, check for a missed timestamp on the final date, text-formatted dates, and ranges that start or end on different rows. In a shared workbook, label the result clearly as the maximum for a specified period so readers do not confuse it with the maximum across the entire dataset.
Frequently Asked Questions
What formula finds the highest value between two dates in Excel?
Use MAXIFS with the values as the maximum range and the date column as a criteria range. For timestamps, set the lower bound to the start date and the upper bound to less than the day after the end date, so the entire last day is included.
Does MAXIFS include the start and end dates?
A greater-than-or-equal start condition includes the start date. A less-than-next-day end condition includes every time on the end date. If your data has dates only, a less-than-or-equal condition on the end date also includes that date.
Why does MAXIFS return 0 when no date matches?
Zero is MAXIFS’s result when no cells meet the criteria. Check for real date values, correct boundary cells, and an interval that actually contains records. If zero or negative values are valid data, do not assume that a returned zero proves a matching record exists.
Can I find the latest date between two dates instead of the highest value?
Yes. Use the date column as the maximum range as well as the criteria range. With the same date bounds, MAXIFS returns the latest qualifying date or timestamp, rather than a measurement from another column.
Can MAXIFS use another condition as well as dates?
Yes. Add another criteria-range and criteria pair, such as a category column and a selected category cell. A matching row must satisfy the date conditions and the additional condition for its value to be considered.
Related
- Excel General Number Format: How It Works and When to Use It
- Excel Number Format: How Many Digits Can It Really Show?
- Excel Formatting Keyboard Shortcuts: A Practical Guide
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Put Emails in Archive in Outlook on Any DeviceTo put an email in the Outlook archive, select the message and choose Archive. Outlook removes it from…
- Multiple Freeze Panes in Excel: What You Can and Can’t DoYou can freeze multiple rows and columns in Excel at the same time, but you cannot create several…
- How to Insert a Line in Word to Type On: Easy MethodsTo make a line you can type on in Word, insert a one-cell table, remove its borders, and…
- Excel Scatter Plot Series Colors: How to Change ThemTo change a scatter plot series color in Excel for Microsoft 365 on Windows, right-click a marker in…
- How to Share a Calendar Invite in Outlook on Any DeviceTo share a calendar invite in Outlook, create or open the event, add the recipients as attendees, then…
- Excel Columns Showing Numbers Instead of Letters?If Excel’s column headings show numbers instead of letters, the workbook is using the R1C1 reference style. To…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.