To average values in Excel only when their dates fall within a chosen range, use AVERAGEIFS with a start-date criterion and an end-date criterion. For example, if dates are in A2:A100, amounts are in B2:B100, and the boundaries are in D2 and E2, the formula is =AVERAGEIFS(B2:B100,A2:A100,">="&D2,A2:A100,"<="&E2). It includes both boundary dates when the cells contain dates without times.
The AVERAGEIFS formula for a date range
AVERAGEIFS calculates the average of numbers that meet one or more conditions. Its argument order is the range to average, followed by a criteria range and its condition, then any further criteria-range and condition pairs. For a date window, the date column is used twice: once to require a date on or after the start, and once to require a date on or before the end.
In the example above, Excel averages the matching numbers in B2:B100. It checks the dates in A2:A100 against the lower boundary in D2 and upper boundary in E2. The criteria operators are joined to the boundary cells with the ampersand, so Excel evaluates the actual date values rather than treating the criteria as unrelated text. The two criteria must both be true for a row to qualify.
Use AVERAGEIFS in Microsoft 365 for Windows; it is also available in current Excel for Mac and Excel for the web. Keep the date and value ranges aligned and the same size. For example, using A2:A100 with B2:B99 can produce a range-size error. If your Excel uses semicolons rather than commas as formula separators because of regional settings, use the separator your installation expects.
Set up dates, values, and boundaries
A typical worksheet has one record per row: a transaction date, visit date, or other date in one column and the number to average in another. Put the beginning and ending dates in two separate cells, rather than typing them into the formula each time. This makes it easy to change the reporting window and helps prevent mistakes when the formula is copied or reused.
- Put genuine Excel dates in A2:A100, not date-looking text.
- Put the numeric measurements or amounts to average in B2:B100.
- Enter the first day of the period in D2 and the last day in E2.
- Make sure D2 is on or before E2; a reversed range will normally have no qualifying rows.
Excel stores dates as serial numbers, which is why it can compare them chronologically. A cell may display a friendly format such as 1/15/2025 while holding a numeric date value underneath. To set a boundary cell to a date format, select it and use Home > Number group > Short Date. Formatting changes how a value appears; it does not turn text into a real date. If a date was imported as text, convert it before relying on date comparisons.
For a reusable report, labels such as Start date and End date beside D2 and E2 make the inputs clear. You can also use an Excel Table so new records are included automatically when data is added, though the formula structure differs if you use structured references. The important principle remains the same: compare the date field to both boundaries and average the corresponding numeric field.
Make the date criteria inclusive or exclusive
The example formula uses greater than or equal to the start date and less than or equal to the end date. That means a record dated exactly on either boundary is included. This is usually what people mean by “between these dates,” especially for daily reports such as a week, month, or quarter.
Sometimes the desired interval is different. If you want dates after the start but not on it, use a greater-than condition for the start. If you want a period that ends just before a new period begins, use a less-than condition for the ending boundary. Being explicit about inclusive or exclusive edges is especially useful when adjacent reporting periods must not overlap.
Time stamps create a common boundary trap. Excel can store a date and time as a number with a fractional part. If E2 contains midnight at the beginning of the final day, a condition of less than or equal to E2 will include that midnight but exclude later times on the same day. For records that include times, use an exclusive boundary at the day after the end date instead.
Assuming dates or timestamps are in A2:A100, values to average are in B2:B100, and D2 and E2 hold the first and last calendar dates, this version includes every time on the end date:
=AVERAGEIFS(B2:B100,A2:A100,">="&D2,A2:A100,"<"&E2+1)
This works because the test stops before midnight on the day after E2. It is generally safer for timestamp data than trying to guess the last possible time of day. Do not use the end-plus-one approach blindly if E2 already contains a specific time that is meant to be the exact cutoff.
Worked example with a real date window
Suppose a small table contains these records: January 5, 10; January 12, 20; January 20, 30; and February 2, 40, where each pair is a date and its value. Set D2 to January 10 and E2 to January 31. Only the January 12 and January 20 rows qualify, so the average is (20 + 30) divided by 2, or 25.
With those four dates in A2:A5, their values in B2:B5, and the boundaries in D2 and E2, use:
=AVERAGEIFS(B2:B5,A2:A5,">="&D2,A2:A5,"<="&E2)
The result is 25. The January 5 value is below the start date, and the February 2 value is beyond the end date, so neither affects the calculation. If you change D2 to January 5, that first value is included because the start condition is inclusive. This is a useful way to check a formula: inspect a few qualifying and excluded rows before trusting a larger report.
There is a related but different question: “What is the average date in this range?” If you mean the midpoint of the dates that fall within the selected window, rather than the average of sales or measurements recorded on those dates, average the date column itself. With dates in A2:A100 and start and end dates in D2 and E2, use =AVERAGEIFS(A2:A100,A2:A100,">="&D2,A2:A100,"<="&E2). Excel returns a date serial; format the result cell as a date to display it in a readable way. This is a numerical mean of the dates, not necessarily a date that appears in the records.
What Excel counts—and what it ignores
In the average range, numeric values are included in the calculation, including zero. Blank cells and text in that range are not counted as numbers. This distinction matters: a zero may mean a genuine measurement of zero, while a blank may mean no measurement was recorded. If blanks actually represent zero in your process, decide deliberately whether to convert them; do not assume Excel treats them as zero.
Rows are selected according to their date cells. A text string that looks like a date may not compare as expected, and a blank date will not satisfy a normal start-date condition. If data has been pasted from a website or exported from another system, check the underlying values rather than relying only on their appearance. A quick clue is alignment: dates stored as text may behave differently from dates entered or recognized as Excel dates.
Also decide how to treat negative values and errors. Negative numbers are numeric and will be included if their row’s date qualifies. An error in the average range can cause the calculation to return an error rather than a useful average. Correct source errors where possible, or apply an intentional data-cleaning rule; suppressing every error can hide a genuine problem in the source.
Troubleshoot common date-range problems
If the formula returns #DIV/0!, Excel found no numeric values to average among the rows that met the criteria. Check that the boundaries are in the intended order, that at least one date falls within them, and that the matching value cells contain numbers. A single qualifying row produces that row’s value as the average; it does not require multiple matches.
If the result seems too high or too low, test the criteria separately by filtering the source table to the same date interval. Confirm whether the end date should be included, and check for timestamps in the date column. A displayed date can conceal a time component, and a value that looks like a date may be text. Also verify that the formula references the intended columns and that every range begins and ends on matching rows.
To show a message instead of the no-match error, you can wrap the calculation. With dates in A2:A100, values in B2:B100, and boundaries in D2 and E2, use:
=IFERROR(AVERAGEIFS(B2:B100,A2:A100,">="&D2,A2:A100,"<="&E2),"No matching records")
This displays the message for any error, not just an empty result. It can therefore conceal other issues, such as a damaged reference. Use it when a friendly report is useful, but investigate unexpected errors rather than automatically treating every error as “no matches.”
Choose between AVERAGEIFS and alternatives
Use AVERAGEIFS when you have a numeric measure and one or more conditions, such as a date window plus a department. Add another criteria range and condition pair for each extra rule. For a single condition, AVERAGEIF is also available, but a date window needs both a lower and an upper condition; AVERAGEIFS is the straightforward choice for that pair.
For more involved reporting in Microsoft 365, a dynamic-array approach using FILTER can make the qualifying records visible as a separate result before averaging them. It is useful when you want to inspect the filtered list or build a more complex calculation. However, it needs a no-match plan because an empty filtered result can return an error, and it is less compact than AVERAGEIFS for a simple date window. FILTER is available in Microsoft 365 and Excel 2021 or later.
For repeatable summaries across many periods, a PivotTable can group date-based records and summarize a numeric field by average. That can be easier for exploring several categories or months, while AVERAGEIFS is often simpler when a report needs one clearly defined start and end date in cells. Whatever method you choose, confirm that the date field is recognized as dates and that the summary is averaging the intended values rather than counting records or summing them.
Frequently Asked Questions
Can I average values between two dates in Excel?
Yes. Use AVERAGEIFS with the date column tested against a start date and an end date, and set the average range to the numeric values you want summarized.
Does AVERAGEIFS include the start and end dates?
It does when the criteria use greater than or equal to the start and less than or equal to the end. If your data includes times, use an exclusive next-day boundary to include the entire final date.
Why does my date-range average show #DIV/0!?
Usually, no qualifying numeric values were found. Check the date boundaries, confirm the dates are real Excel dates, and verify that matching rows have numbers in the average range.
Can Excel average dates instead of values recorded on dates?
Yes. Set the AVERAGEIFS average range to the date column itself. The result is an average date serial, so format the result cell as a date to read it normally.
Does AVERAGEIFS count blank cells or zero values?
Blank cells and text in the average range are ignored, while numeric zeros are included. Check your source data if a blank is meant to represent a measured zero.
Related
- Excel Attendance Format: A Clear Monthly Attendance Tracker
- Excel Attendance File to Print: Set Up and Print It
- Excel Timesheet Format: Build a Reliable Weekly Sheet
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Add a Formula in Excel to Calculate DatesTo calculate a date in Excel, enter a starting date in one cell and use a formula such…
- Excel Date Minus Days: Formulas and ExamplesTo subtract calendar days from a date in Excel, subtract a number from the date cell: if the…
- Calculate Age in Excel in Years with Simple FormulasTo calculate completed age in years in Excel, put each birth date in column A and, with the…
- Excel Day Name Formula: Show the Weekday from a DateTo show the weekday name for a date in Excel, enter =TEXT(A2,"dddd") in another cell, assuming A2 contains…
- How to Create Round Labels in Word: A Step-by-Step GuideTo create round labels in Word, set up a label sheet using the stock’s template or measurements, place…
- How to Copy Formulas in Excel on a MacBook Without ErrorsTo copy a formula in Excel on a MacBook, select the cell containing it, press Command+C, select the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.