To find the largest value recorded between two dates in Excel, use =MAXIFS(C2:C100,A2:A100,">="&E2,A2:A100,"<="&F2). This assumes dates are in A2:A100, the values to compare are in C2:C100, and the inclusive start and end dates are in E2 and F2.
The MAXIFS formula and a worked example
MAXIFS returns the maximum number from a range after checking one or more conditions. In the example above, Excel looks down the date column, keeps rows on or after the start date and on or before the end date, then returns the greatest corresponding value. The criteria are inclusive: records dated exactly on either boundary are eligible.
Suppose A2:A6 contains May 1, May 3, May 8, May 10, and May 12, while C2:C6 contains 120, 185, 142, 210, and 175. If E2 is May 3 and F2 is May 10, the eligible values are 185, 142, and 210. The result is 210. The May 1 and May 12 records fall outside the selected period.
For a smaller table, use ranges that match the actual data, such as A2:A6 and C2:C6. For a growing table, an Excel Table can make the formula easier to maintain: structured references adjust as rows are added. Keep the date and value ranges aligned and the same size; mismatched ranges can cause an error.
How the date criteria work
Excel stores valid dates as serial numbers, which lets comparison operators test whether a date falls before, after, or within a range. The text criteria ">="&E2 and "<="&F2 combine an operator with the date in the relevant cell. Using cell references means you can change the period without editing the formula.
Enter actual Excel dates in E2 and F2 rather than date-looking text. A cell may display as “May 3” while storing a valid date value; formatting controls its appearance, not its underlying value. If a date was imported as text, comparisons may fail or behave inconsistently. Changing the cell’s number format alone does not convert text into a real date.
To verify a suspicious date, select its cell and temporarily apply a date or General number format. A real Excel date will display as a date or serial number; text may remain text. Use a proper conversion step for imported data, and confirm that the result is recognized as a date before relying on the maximum.
Find a maximum when records include times
A date-time value includes a fractional part for the time. If F2 contains May 10 at midnight, a criterion of less than or equal to F2 excludes May 10 records later that day. This is a frequent reason the apparent last day of a period seems to be missing.
With timestamps in A2:A100, values in C2:C100, and date-only boundaries in E2 and F2, use this version to include the whole end date:
=MAXIFS(C2:C100,A2:A100,">="&E2,A2:A100,"<"&(F2+1))
The upper condition is strictly before the day after F2. For example, if F2 is May 10, a record at 11:30 p.m. on May 10 is included, while a record on May 11 is not. This approach is usually clearer and safer than trying to guess the last possible time value of the selected day.
If the data contains dates only, the original less-than-or-equal-to formula is appropriate. If E2 and F2 themselves include times, decide whether the intended range is a precise time interval or full calendar days, then set the criteria to match that definition.
Set up the sheet and choose the right formula
For Microsoft Excel for Microsoft 365 on Windows, organize the data so each record has a date and a numeric value. Put the start and end dates in two separate cells, then enter the formula in a result cell. Format the result as Number, Currency, or another suitable format; formatting changes how the answer appears, not which records are considered.
- Put each record’s date in one column and its value in another. Keep dates and values on the same row.
- Enter the beginning and ending dates in separate cells, such as E2 and F2.
- Use
MAXIFSwith the corresponding date and value ranges. - Check that the date order is sensible and that both boundaries are included if that is what you intend.
- Test the result against a few eligible rows, especially the dates at the start and end of the period.
MAXIFS is available in Microsoft 365 and modern perpetual Excel editions including Excel 2019, Excel 2021, and Excel 2024. It is also available in current Excel for Mac. The formula syntax is the same on Windows and Mac; no special ribbon command is needed. In Excel for the web, enter the formula in a cell as usual. Some regional settings use semicolons instead of commas between arguments, so a formula copied from a comma-based example may need its separators changed to match the installation.
What to use in older Excel versions
Excel versions without MAXIFS can calculate a conditional maximum with an array formula. With dates in A2:A100, values in C2:C100, and the start and end dates in E2 and F2, use:
=MAX(IF((A2:A100>=E2)*(A2:A100<=F2),C2:C100))
In older Excel versions that do not support dynamic array calculation, confirm this formula with Ctrl+Shift+Enter rather than Enter alone. Excel places braces around an array formula when it is entered correctly; do not type the braces yourself. In Microsoft 365 and other versions with dynamic arrays, Enter is sufficient. As with the MAXIFS method, the ranges must correspond row by row, and the date cells must contain real dates.
The array formula returns the maximum matching numeric value, but it can be less straightforward to troubleshoot and maintain. If a workbook is shared with people using mixed Excel versions, check which functions their software supports before choosing a formula. A normal MAXIFS formula is easier to read when everyone who needs the workbook has a compatible edition.
Add another condition, such as a category
You can add more criteria to limit the search. For example, a sales log might have dates in A2:A100, amounts in C2:C100, and product categories in D2:D100. If H2 contains the category to include, this formula finds the largest amount in the selected date range for that category:
=MAXIFS(C2:C100,A2:A100,">="&E2,A2:A100,"<="&F2,D2:D100,H2)
Each extra condition is supplied as a criteria range followed by its criterion. In this example, a row must meet both date tests and have a category equal to H2. The conditions work together, so the formula does not first find an overall maximum and then check its category; it searches only rows that satisfy all the supplied tests.
Additional criteria can be useful for a particular store, employee, product, or status. Ensure the new criteria range covers the same rows as the date and value ranges. If category labels contain inconsistent spelling or extra spaces, matching may exclude records that look similar to a person but are not identical text.
Common problems and how to check them
- The answer is zero. MAXIFS returns zero when there are no qualifying numeric values. Check the date boundaries, whether dates are stored as text, and whether the matching value cells contain numbers rather than text. A genuine maximum can also be zero or negative, so do not treat zero alone as proof that there are no records.
- The last day is missing. Check whether the date column contains times. If it does, use the less-than-next-day condition described above for a date-only end boundary.
- The result is unexpectedly low. Confirm the maximum range points to the intended value column, and verify that no values were imported as text. Also check that E2 is not later than F2 and that the selected date range includes the intended records.
- The formula gives an error or is rejected. Check for equal-sized ranges, balanced parentheses, and separators appropriate to your regional settings. Excel installations configured for semicolon separators may require semicolons in place of commas.
- Dates display differently from the formula’s expectations. Display formats do not change stored values. Inspect imported date data and convert text dates before using date comparisons.
If you need to distinguish “no matching records” from a legitimate maximum of zero, count matching dates separately or build a clear no-match check into the result. With dates in A2:A100 and boundaries in E2 and F2, this formula checks whether any date rows fall in the inclusive period before calculating the maximum in C2:C100:
=IF(COUNTIFS(A2:A100,">="&E2,A2:A100,"<="&F2)=0,"No matching dates",MAXIFS(C2:C100,A2:A100,">="&E2,A2:A100,"<="&F2))
This check detects the absence of matching dates, not the absence of numeric values among matching rows. If records can have blank or nonnumeric values, inspect those cells as well before interpreting the result.
Maximum value versus the row that contains it
A maximum formula returns the value, not the date or other details from the row where that value occurred. If several records share the same maximum, the number alone cannot tell you which record—or how many records—matched. This distinction matters in reports where the reader needs the transaction, location, or timestamp behind a peak.
For a simple audit, filter the source table to the same date interval and sort the value column from largest to smallest. That makes it possible to inspect tied values and their associated rows. If you use a separate lookup formula to retrieve a matching record, decide how ties should be handled; a lookup may return one matching row rather than every row with the maximum.
Frequently Asked Questions
How do I find the highest number between two dates in Excel?
Use MAXIFS with the values as the maximum range and the date column as the criteria range. Set one date condition to greater than or equal to the start and another to less than or equal to the end. Use the less-than-next-day approach if timestamps are present and the whole final day should count.
Does MAXIFS include the start and end dates?
Yes, when the criteria use greater than or equal to the start and less than or equal to the end. Those operators include records exactly on both boundary dates. Time components can affect the end boundary, so account for timestamps when relevant.
Can I find a maximum between dates when my data includes times?
Yes. Set the lower limit to the start date and the upper limit to strictly before the day after the end date. That includes all times on the selected end date without including midnight or later on the following day.
Why does MAXIFS return 0 when there are matching dates?
There may be no numeric values in the matching rows, the value range may point to the wrong cells, or the date conditions may not match the stored dates. MAXIFS can also legitimately return zero when zero is the largest qualifying number. Check the underlying cell values, not only their display formats.
What if my Excel does not have MAXIFS?
Use the array formula shown above. In older Excel versions, enter it with Ctrl+Shift+Enter; in Microsoft 365 or another dynamic-array version, Enter is sufficient. Confirm compatibility if the workbook will be opened in different Excel editions.
Related
- How to Ungroup Rows in Excel With a Keyboard Shortcut
- How to Undo in Excel With a Keyboard Shortcut on Windows
- How to Undo in Excel After Saving Your Workbook Changes
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Microsoft Excel Grade Formula for Scores and LettersIn Microsoft Excel, calculate a percentage grade by dividing points earned by points possible: with points earned in…
- How to Share an Outlook Calendar With the Right PermissionsTo share an Outlook calendar, open Calendar, choose Share Calendar, enter the recipient’s email address, set what they…
- Excel Formula Bar: What It Shows and How to Use ItIn Excel, the Formula Bar is the field above the worksheet that shows the contents of the selected…
- Excel SUMIFS: Sum Values That Meet Multiple CriteriaTo sum values in Excel when several conditions must be true, use SUMIFS. For example, with regions in…
- Log Functions in Excel: How to Calculate LogarithmsTo calculate a logarithm in Excel, use LOG: with the value in A2 and the base in B2,…
- Apex String Functions: Common Methods and ExamplesIn Salesforce Apex, “string functions” usually means methods on the built-in String type. Use methods such as trim(),…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.