To find the largest value in B2:B100 for records dated between the start date in D2 and the end date in E2, use =MAXIFS(B2:B100,A2:A100,">="&D2,A2:A100,"<="&E2). Here, A2:A100 contains the dates and B2:B100 contains the values; the date limits are included.
What “maximum between two dates” can mean
The phrase can refer to two different results. You might want the highest measurement, sale, score, or other value recorded during a date range. Or you might want the latest date that falls within that range. Excel can calculate either, but the formula’s maximum range must match the result you want.
For example, if column A contains transaction dates and column B contains amounts, the first formula returns the largest amount for transactions in the selected period. It does not return the date of that transaction. If several transactions share the maximum amount, the result is still just the amount; finding the associated date requires a separate lookup.
In Microsoft 365 for Windows, MAXIFS is usually the clearest option for finding a maximum subject to date criteria. It checks the date conditions on each row and considers the corresponding value in the maximum range.
Set up the data and date limits
Use one column for dates and another for the numbers being compared. Put the beginning and ending dates in separate cells so you can change the period without editing the formula. For example, A2:A7 can contain dates, B2:B7 can contain readings, D2 can contain the start date, and E2 the end date.
Suppose the rows contain Jan 3 with 12, Jan 8 with 19, Jan 12 with 14, Jan 12 with 24, Jan 20 with 17, and Feb 2 with 31. If D2 is Jan 1 and E2 is Jan 31, the maximum is 24: it is the largest value on a date in January. The February value of 31 is outside the period and is excluded.
- Enter real Excel dates in the date column and limit cells, rather than text that merely looks like a date.
- Keep the date and value ranges aligned: each date should describe the value on the same row.
- Make the start date earlier than or equal to the end date. A reversed range has no qualifying rows.
Excel stores genuine dates as serial numbers, which lets comparison criteria work chronologically. A cell’s display format can make a real date look like text, but formatting alone does not turn text into a date. If imported dates do not behave as expected, check their underlying values and convert text dates before relying on the result.
How MAXIFS works
MAXIFS takes a range of numbers to maximize, followed by one or more pairs of criteria ranges and criteria. In this case, the date range is used twice: once to require a date on or after the start, and again to require a date on or before the end. The ampersand joins each comparison operator to the date held in a cell.
With dates in A2:A7, values in B2:B7, the start date in D2, and the end date in E2, this version produces 24 for the January example:
=MAXIFS(B2:B7,A2:A7,">="&D2,A2:A7,"<="&E2)
The operator >= includes the starting date, and <= includes the ending date. This is usually what people mean by “between two dates.” If the range should exclude one or both endpoints, change the relevant comparison operator to > or <.
The maximum range and each criteria range need compatible dimensions. For example, B2:B100 and A2:A100 both cover 99 rows. If one range starts at row 2 and another at row 3, the formula may return an error because the ranges do not line up.
Handle times as well as dates
A date-time value contains both a date and a time. If a record on the end date is stored as Jan 31 at 3:00 p.m., it is greater than the date-only value Jan 31, which represents the start of that day. As a result, the inclusive-looking condition <=E2 can exclude records later on the end date when E2 contains no time.
For data that may include times, a reliable approach is to include everything before the day after the end date. With dates or date-times in A2:A100, values in B2:B100, and date-only limits in D2 and E2, use:
=MAXIFS(B2:B100,A2:A100,">="&D2,A2:A100,"<"&(E2+1))
This includes times throughout E2’s calendar day but excludes midnight at the beginning of the following day. The same pattern is useful for daily reports. It assumes E2 is a date-only limit; if the limit cell intentionally contains a particular time, decide whether that exact time should be included and set the criteria accordingly.
Return the latest date instead of the highest value
If the dates themselves are the values you want to maximize, make the date column the maximum range as well as the criteria range. With dates in A2:A100 and the start and end dates in D2 and E2, this returns the latest date within the inclusive period:
=MAXIFS(A2:A100,A2:A100,">="&D2,A2:A100,"<="&E2)
Format the result cell as a date if Excel displays a serial number. Select the cell and use Home > Number Format to choose a date format. On Windows, Ctrl+1 opens Format Cells; on Mac, Command+1 opens the corresponding dialog. This formatting changes how the result appears, not the calculation.
Do not confuse the latest qualifying date with the date associated with the largest value. If the highest reading is 24 on Jan 12, but a later reading is 17 on Jan 20, the maximum value is 24 and the latest date is Jan 20. They answer different questions.
What happens when there are no matches
MAXIFS returns 0 if no cells meet the criteria. That can be misleading if zero is a valid result or if the real values may all be negative: a returned zero might mean there were no qualifying records, not that a qualifying value was zero. Check that the chosen period actually contains records before interpreting the result.
To display a blank when there are no qualifying dates, use a count check around the maximum calculation. With dates in A2:A100, values in B2:B100, and date-only limits in D2 and E2, this formula returns a blank string when the period has no matching rows:
=IF(COUNTIFS(A2:A100,">="&D2,A2:A100,"<="&E2)=0,"",MAXIFS(B2:B100,A2:A100,">="&D2,A2:A100,"<="&E2))
If your date cells can contain times and you want to include the full end date, use the same less-than-next-day boundary in both the count and maximum tests. A blank result is useful for reports, but it is different from a numeric zero and may affect later calculations that expect a number.
Common problems and checks
- Dates stored as text: A date that looks correct may not compare as a date. Check whether sorting the column places dates in chronological order and whether Excel recognizes the cells as dates.
- End-day records missing: Check for time portions in the date column. Use the less-than-next-day approach when the full end date should count.
- Wrong result range: Confirm that the first argument is the values to maximize, not the date range, unless you specifically want the latest date.
- Misaligned ranges: Make sure the maximum range and criteria range cover the same number of rows and that each row’s date belongs to its value.
- Unexpected zero: Confirm that qualifying rows exist and distinguish a no-match result from a genuine zero value.
- Blank or erroneous source cells: Review the source data and make sure the values meant to be compared are numeric. Clean up errors or inconsistent imports if they prevent the calculation from representing the intended records.
For a formula that should expand as new records are added, consider converting the data to an Excel table with Insert > Table. Table references can grow with added rows, reducing the chance that a fixed range such as A2:A100 leaves out newer data. When using tables, confirm that the formula refers to the date and value columns you intend.
Alternatives in older Excel versions
MAXIFS is available in Microsoft 365 and newer perpetual Excel releases, but it may be absent from older installations. If the function name produces #NAME?, check the Excel version before assuming the date data is faulty. One alternative is an array formula using MAX and IF.
With dates in A2:A100, values in B2:B100, and limits in D2 and E2, the following formula tests each row and takes the maximum qualifying value:
=MAX(IF((A2:A100>=D2)*(A2:A100<=E2),B2:B100))
In Microsoft 365, press Enter to enter this formula. In older Excel versions that require legacy array entry, confirm it with Ctrl+Shift+Enter on Windows rather than Enter alone; Excel then displays braces around the formula in the formula bar, and you should not type those braces yourself. On Mac, the equivalent legacy array-entry shortcut is Control+Shift+Return. This approach is less straightforward than MAXIFS, so check the result carefully, especially when no rows match.
Frequently Asked Questions
Does MAXIFS include both dates in the range?
Yes, if the criteria use >= for the start and <= for the end. If the date column contains times on the end date, use a less-than comparison against the day after the end date to include the whole day.
How do I find the maximum date between two dates?
Use the date column as the maximum range and apply the same date boundaries to it. The result is the latest date in the period, not the row’s associated value. Format the result as a date if it appears as a number.
Why does MAXIFS return 0?
It returns 0 when no records meet the criteria. Verify the date limits, the date values, and whether times are present. If zero is ambiguous in your data, use a count check to return a blank or another clearly distinguishable result for an empty period.
Can I use MAXIFS with negative numbers?
Yes. If qualifying cells contain negative numbers, MAXIFS can return the greatest of them, meaning the number closest to zero. A zero result can still indicate that no rows matched, so verify that the period contains data.
What if my Excel does not recognize MAXIFS?
Check whether your Excel version includes the function. In older versions, an array formula using MAX and IF can calculate a conditional maximum; some older Excel releases require Ctrl+Shift+Enter on Windows or Control+Shift+Return on Mac to enter it.
Related
- Excel German Number Format: Commas, Dates, and Currency
- Excel General Number Format: How It Works and When to Use It
- Excel Number Format: How Many Digits Can It Really Show?
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Conditional Formatting to Highlight Blank CellsYes. In Excel for Microsoft 365 on Windows, select the range and choose Home > Conditional Formatting >…
- How to Remove a Section Break in Word and Keep FormattingTo remove a section break in Word, show formatting marks, select the break, and press Delete (or place…
- Google Sheets Dropdown Based on Another Cell: GuideYes. In Google Sheets, you can make one dropdown depend on another by creating named ranges for each…
- Excel Not Letting Me Insert a Row? Fixes That WorkIf Excel won’t let you insert a row, select the row number where it should go, then choose…
- Excel Business Contact List Template: Fields and Setup GuideAn Excel business contact list template is a structured worksheet for storing and maintaining names, companies, email addresses,…
- What Is Google Sheets and How Does It Work?Google Sheets is Google’s online spreadsheet application. It lets you enter and organize information in a grid, calculate…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.