Excel AVERAGEIF With Dates: Formula, Examples, and Tips

Formulas & functions · Updated November 19, 2026

Yes—Excel’s AVERAGEIF can average values for a matching date. With dates in A2:A100, values in B2:B100, and the date to match in D2, use =AVERAGEIF(A2:A100,D2,B2:B100).

For a date span, or dates that include times, use AVERAGEIFS with a start and end boundary; it can apply more than one condition. The key is that Excel must recognize the dates as real date values, not merely text that looks like a date.

How AVERAGEIF works with dates

AVERAGEIF has three arguments: the range to test, the criterion to apply, and the range of numbers to average. In the opening example, Excel checks each cell in A2:A100 for the date in D2, then averages the corresponding value from B2:B100 for every match. If the average-range argument is omitted, AVERAGEIF averages the cells in the criteria range itself, which is rarely what you want when that range contains dates.

Excel stores valid dates as numbers and displays them in a date format. That lets a date cell serve as a criterion just like a number or text value. If the date in D2 is January 5, 2025, the formula matches entries with that exact date value. It does not mean “any time during January 5” if the cells also contain time-of-day values.

The date criterion can be entered directly in a cell, which is usually easier to maintain than typing a date into the formula. It also avoids ambiguity between regional date conventions, such as month/day/year and day/month/year. Format the criterion cell as a date so it is easy to read; formatting changes its display, not the underlying value.

Average values for one exact date

Suppose A2:A7 contains January 5, January 5, January 6, January 7, January 5, and January 8, 2025, while B2:B7 contains 10, 20, 30, 40, 50, and 60. Put January 5, 2025 in D2. With those dates and values in the stated cells, use =AVERAGEIF(A2:A7,D2,B2:B7). The result is 26.6667 (or 26.67 when displayed to two decimal places): the matching values are 10, 20, and 50, whose total is 80, divided by 3.

This is an exact-match calculation. AVERAGEIF does not interpret a date in D2 as a whole-day range. If a source value is January 5 at 9:00 a.m., it is numerically different from midnight at the start of January 5, even if the cell is formatted to show only the date. That distinction matters for imported logs, transaction timestamps, and exported system data.

Average between two dates

For a period, specify a lower and upper boundary. AVERAGEIF accepts just one criterion, so it cannot by itself test both “on or after the start” and “before the end.” AVERAGEIFS is designed for multiple criteria and is a practical choice for a date interval.

With dates in A2:A100, values in B2:B100, the start date in E2, and the inclusive end date in F2, use =AVERAGEIFS(B2:B100,A2:A100,">="&E2,A2:A100,"<"&F2+1). This includes dates from the start of E2 through the end of F2, including timestamps on the end date. For example, if E2 is January 5 and F2 is January 7, the formula includes every time on January 5, 6, and 7, but excludes January 8.

The less-than-next-day boundary is intentional. A condition of “less than or equal to January 7” can exclude January 7 records with a time later than midnight, because those values are slightly greater than the date’s midnight value. Using the next day as an exclusive upper limit handles both date-only values and timestamps cleanly. In the sample data above, the January 5–7 interval includes 10, 20, 30, 40, and 50, so the average is 30.

AVERAGEIF or AVERAGEIFS?

Choose AVERAGEIF when there is one condition, such as an exact date, a date on or after a boundary, or a date before a boundary. Choose AVERAGEIFS when you need two boundaries for a period or want to combine a date condition with another condition, such as a department, product, or status.

The argument order differs. AVERAGEIF starts with the criteria range, then the criterion, then the optional average range. AVERAGEIFS starts with the average range, followed by pairs of criteria ranges and criteria. This difference is easy to overlook when switching between the functions. Keep each criteria range the same size and shape as the average range; mismatched ranges can produce unexpected results or errors.

For a single date criterion, prefer a date cell such as D2 over a typed text criterion. For a date period, use explicit start and end criteria rather than trying to combine a range into one AVERAGEIF condition. If you also need a category, add another criteria-range-and-criterion pair to AVERAGEIFS.

Average by month or year

To average a calendar month, use the first day of the month as the inclusive lower boundary and the first day of the next month as the exclusive upper boundary. That approach works even when the date column contains timestamps and avoids having to calculate how many days are in each month.

With dates in A2:A100 and values in B2:B100, this formula averages all entries in January 2025: =AVERAGEIFS(B2:B100,A2:A100,">="&DATE(2025,1,1),A2:A100,"<"&DATE(2025,2,1)). In the six-row sample, all six dates are in January and the values total 210, so the result is 35. For another month, change the two boundary dates so the second is the first day of the following month.

A year can be handled the same way: use January 1 of the target year as the start and January 1 of the next year as the exclusive end. This is often safer than trying to match a displayed year, because Excel dates include a full date value rather than a separate year field. If your data spans several years, the boundaries ensure that January 2025 is not mixed with January in another year.

Check that your dates are real Excel dates

A date may look correct but be stored as text, especially after copying data from a website, CSV file, or another system. If AVERAGEIF returns no matches, first check whether Excel recognizes the source dates and the criterion date as numeric date values. A quick clue is alignment: numbers and dates often align right by default, while text commonly aligns left, though formatting and manual alignment can change that clue.

Useful checks and cleanup steps include:

  • Change a test cell’s number format to General. A recognized date will usually display as a serial number; text will remain text.
  • Re-enter one date directly in Excel or use a date value in a separate criterion cell to see whether the match works.
  • For text imported into a worksheet, select the affected cells and use Data > Text to Columns when the data is consistently delimited and the date format can be interpreted correctly.
  • Check regional date order before converting ambiguous text such as 03/04/2025; it could mean March 4 or April 3 depending on the source convention.

Do not convert dates simply by changing their cell format. Number formatting changes how a stored value appears; it does not turn arbitrary text into a date. If you import dates regularly, set or verify the correct date interpretation during the import process.

Common errors and unexpected results

A #DIV/0! result commonly means the criteria matched no numeric cells to average. Check spelling or date values, the selected ranges, and whether the matching rows have numbers in the average range. A blank result is not always an error: a matching row may have a blank in its average cell, leaving no numeric values to average.

Other common causes of a surprising answer include:

  • Times hidden by formatting: Dates that display identically may have different time values. Use a start boundary and an exclusive next-day boundary to capture a full day.
  • Text in the average range: AVERAGEIF averages numeric entries; text and blank cells in the average range are not treated as numeric measurements. Numeric zeroes do count and can lower the result.
  • Different range sizes: Make sure the criteria range and average range line up row for row and cover the same number of cells.
  • Wrong date interpretation: A text date may be read in a different month/day order than intended, or may not be recognized at all.
  • Boundary choice: For an inclusive end date that may contain times, use the next day as an exclusive upper boundary rather than a midnight-only upper limit.

Also check for errors already present in the source cells and confirm that the criterion is the intended date, not a date displayed similarly with a different year. If the result seems mathematically wrong, filter or inspect the rows that meet the date condition and verify which numeric values are being averaged.

When another approach is useful

AVERAGEIF and AVERAGEIFS are suitable for a formula-based summary that updates as the source data changes. If you need a visible report by month, category, or other grouping, a PivotTable may be easier to explore and rearrange. A PivotTable can group date fields by periods such as months or years, provided the source column contains valid dates; refresh it when the underlying data changes.

For a simple one-time check, filtering the date column and calculating an average from the visible values may be enough, but it is less reusable than a formula summary. For a recurring worksheet, keep the data in a clearly structured list with one date per row and separate columns for the measured values and categories. Avoid mixing subtotals or explanatory text into the data range used by the calculation.

Frequently Asked Questions

Can AVERAGEIF match a date stored in another cell?
Yes. Use the criterion cell as the second argument, as in the opening example. The criterion cell and source dates must represent the same date value; identical-looking text and dates are not necessarily equal to Excel.

How do I average a date range including both the start and end dates?
Use AVERAGEIFS with a criterion greater than or equal to the start date and a criterion less than the day after the end date. This includes all timestamps on the end date as well as date-only entries.

Can AVERAGEIF average dates in one column and values in another?
Yes. Set the date column as the criteria range and the numeric column as the average range. Each date row then determines whether the corresponding value is included.

Why does AVERAGEIF return #DIV/0! for a date?
Usually there are no matching rows with numeric values to average. Check that the dates are real Excel dates, that the criterion is correct, and that the corresponding cells in the average range contain numbers.

Does AVERAGEIF include times on the chosen date?
An exact date criterion generally matches the date value at midnight, not every timestamp during that day. To include every time on a date, use AVERAGEIFS with a lower boundary at that date and an exclusive upper boundary at the following date.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.