Excel Find Max Value in a Date Range

Updated 9 November 2026 · Standard formula, shown below

To find the largest value recorded between two dates in Excel, use MAXIFS with the value column and date-range criteria. For example, =MAXIFS(B2:B100,A2:A100,">="&E1,A2:A100,"<="&F1) returns the maximum from B2:B100 where the corresponding dates in A2:A100 fall on or after the start date in E1 and on or before the end date in F1.

How the formula works

MAXIFS evaluates a range of numbers and returns the largest number whose corresponding rows meet every specified condition. Its basic syntax is:

=MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

max_range contains the values to compare, such as sales, temperatures, or quantities.
criteria_range contains the dates to test. It must align row by row with the maximum range.
criteria sets a condition. Join comparison operators such as >= to a cell reference with &.

In the example, E1 is the start date and F1 is the end date. The two criteria apply to the same date range: one includes dates greater than or equal to E1, and the other includes dates less than or equal to F1. Both conditions must be true for a value to be considered.

Set up the data and enter the formula

Put dates and their associated values in aligned columns. For example, dates might be in A2:A100 and daily sales in B2:B100, with the chosen start and end dates in E1 and F1. Keep headers outside the ranges.

Enter a valid start date in E1 and an end date in F1.
Click the cell where you want the answer.
Enter =MAXIFS(B2:B100,A2:A100,">="&E1,A2:A100,"<="&F1).
Press Enter. Format the answer as a number, currency, or other suitable display format if needed.

In locales that use semicolons as formula separators, replace the commas with semicolons. The criteria operators and cell-reference concatenation remain the same. If your spreadsheet uses an Excel table, structured references can make the formula easier to maintain; the essential requirement is still that the date and value columns correspond to the same records.

Inclusive dates and timestamps

The formula above includes both boundary dates. That is usually what people mean by “between” two dates. Excel stores dates as serial numbers, so comparison operators test their underlying values, not the way dates look on screen.

A frequent complication is that a date column may contain times as well as dates. A cell displayed as 8/15/2026 might actually contain 8/15/2026 14:30. The criterion <="&F1 compares against midnight at the start of the end date, so it can exclude later timestamps on that date.

For date-time entries, a reliable approach is to include everything before the day after the end date. If E1 and F1 contain date-only values, use:

=MAXIFS(B2:B100,A2:A100,">="&E1,A2:A100,"<"&(F1+1))

This includes timestamps at any time on F1 while excluding midnight on the following day and later. It also avoids assumptions about the smallest time increment in the data.

Excel versions and alternative formulas

MAXIFS is available in Microsoft 365 and Excel 2019 and later. If the function is unavailable in an older Excel version, use an array formula such as:

=MAX(IF((A2:A100>=E1)*(A2:A100<=F1),B2:B100))

In older Excel versions, confirm this type of array formula with Ctrl+Shift+Enter rather than Enter alone. Excel adds braces around the formula when it recognizes it as an array formula; do not type those braces yourself. In current versions with dynamic arrays, Enter is generally sufficient.

Another option is to filter the records to the date range and use MAX on the visible values, but ordinary MAX does not automatically ignore hidden rows. For repeatable calculations, MAXIFS is typically clearer and less error-prone than manually filtering.

Common problems and checks

Dates are stored as text. A date that looks correct may not be a numeric Excel date. Convert text dates to real dates before comparing them; changing the cell’s display format alone does not convert the underlying value.
Ranges have different sizes. The maximum range and each criteria range should cover matching rows. For example, avoid pairing B2:B100 with A2:A99.
The answer is zero. If no row meets the conditions, or if eligible values are all negative, MAXIFS returns 0. Check the date bounds and source data rather than assuming zero means the actual maximum is zero.
Numbers are stored as text. Text values in the maximum range are not treated as ordinary numeric values. Convert them to numbers if they should be included.
Start date is later than end date. No row can satisfy both conditions. Check the two input dates, especially if they are entered manually.
Unexpected boundary results. Check whether the date cells contain hidden times and decide whether the final day should be included in full.

Do not use a date range in the maximum range unless the goal is to return the latest date. To find the greatest sales figure, the maximum range must be the sales column, while the date column is used only for the criteria.

Frequently Asked Questions

How do I find the maximum value between two dates in Excel?
Use MAXIFS with the values as the maximum range and the dates as criteria: =MAXIFS(B2:B100,A2:A100,">="&E1,A2:A100,"<="&F1).

Does MAXIFS include the start and end dates?
Yes. The operators >= and <= include records on both boundary dates, provided the date cells do not contain a time that changes the comparison.

How can I include the whole end date when my data has times?
Use a less-than condition against the next day: "<"&(F1+1). This includes every timestamp on the end date.

Why does MAXIFS return 0 when there are negative values?
If no row matches the criteria, MAXIFS returns 0, even if other values are negative. Verify that the date criteria match the data and that the dates are real Excel dates.

Can I use MAXIFS for multiple conditions?
Yes. Add another criteria-range and criteria pair for each condition, such as a product column and a product name. All supplied conditions must be met.

What if my Excel version does not recognize MAXIFS?
Use an array formula with MAX(IF(...)) in older versions, confirming it with Ctrl+Shift+Enter where required, or update to an Excel version that supports MAXIFS.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.