Average Between Two Dates in Excel: Formulas & Tips

Formulas & functions · Updated November 19, 2026

To average values whose dates fall between two dates in Excel, use =AVERAGEIFS(B2:B100,A2:A100,">="&E2,A2:A100,"<="&F2). This averages numbers in B2:B100 where the corresponding dates in A2:A100 are on or after the start date in E2 and on or before the end date in F2.

Set up the data and date limits

Use one column for dates and another for the values you want to average. For example, column A might contain transaction dates and column B the amounts, hours, temperatures, or other measurements recorded on those dates. Put the first date of the period in E2 and the last date in F2. The example formula assumes those exact ranges; adjust them to fit your worksheet.

Enter valid Excel dates in the date column and in E2 and F2. Excel stores dates as serial numbers, even though it displays them in familiar formats such as 11/3/2026 or Nov 3, 2026. A cell that merely looks like a date may instead contain text, which can prevent date criteria from matching. Applying a date number format changes how a valid date appears; it does not convert text into a genuine date.

Before relying on the result, check that the start date is not later than the end date and that each value in a date row corresponds to the value in the same row of the average range. If your data is in an Excel Table, you can also use structured references, but the ordinary cell-range version is straightforward and works in Microsoft 365 for Windows.

How AVERAGEIFS applies the date criteria

AVERAGEIFS calculates the mean of values that meet one or more conditions. In this case, the first argument is the range to average, B2:B100. The next arguments identify a range to test, followed by a condition. Because both boundaries apply to the same date column, the date range and its criterion appear twice.

The criteria are built by joining a comparison operator to a cell reference. The expression ">="&E2 means “greater than or equal to the date in E2,” while "<="&F2 means “less than or equal to the date in F2.” The ampersand joins the operator text to the date value. Using cell references means you can change the period without rewriting the formula.

Both boundaries are inclusive: records dated exactly on the start date or end date qualify. AVERAGEIFS also requires the average range and each criteria range to have matching dimensions. If one range has 99 rows and another has 100, Excel returns an error rather than pairing the records as intended.

Worked example with a date range

Suppose A2:A5 contains Jan 1, Jan 3, Jan 5, and Jan 6, while B2:B5 contains 10, 20, 30, and 40. Put Jan 3 in E2 and Jan 5 in F2. With dates in A2:A5, values in B2:B5, and the limits in E2 and F2, use =AVERAGEIFS(B2:B5,A2:A5,">="&E2,A2:A5,"<="&F2). The qualifying values are 20 and 30, so the result is 25.

The Jan 1 and Jan 6 rows are excluded because they fall outside the chosen period. The formula averages qualifying rows, not the dates themselves. If there are multiple measurements on Jan 3, each matching row contributes separately. For example, values of 20 and 40 recorded that day both count in the overall mean unless you first summarize the data by day.

To make the period easier to inspect, label E1 “Start date” and F1 “End date,” and use consistent date formatting for the input cells. Avoid typing date limits directly into the formula: date interpretation can vary with regional settings, and cell references are easier to update and audit.

Including every time on the end date

A common complication is that the date column contains timestamps, not dates at midnight. Excel stores a date and time as a number with a fractional part for the time. A value recorded on the end date at 3:00 p.m. is therefore greater than the date in F2, which represents midnight at the start of that day. The inclusive “less than or equal to” test can omit later timestamps on the final date.

For timestamped records, use a start-inclusive and next-day-exclusive boundary. With timestamps in A2:A100, values in B2:B100, the start date in E2, and the final calendar date in F2, use =AVERAGEIFS(B2:B100,A2:A100,">="&E2,A2:A100,"<"&F2+1). The less-than test includes every time on the end date but excludes midnight at the beginning of the following day.

This pattern assumes F2 contains a date without a time. If F2 itself includes a time, adding 1 extends the cutoff by a full day from that timestamp, which may not match the intended period. Check how the boundary cells are entered, and use a date-only end-date cell when you want to include the complete final calendar day.

Choose the average that answers your question

AVERAGEIFS gives each qualifying numeric row equal weight. That is appropriate when every row is one observation of equal importance. It does not automatically calculate a daily average first, nor does it weight observations by the amount of time between them.

  • Average of all observations: Use AVERAGEIFS when each recorded measurement should count once, even if some dates have more records than others.
  • Average of daily averages: Summarize the measurements for each day first, then average those daily results if each day should have equal weight.
  • Time-weighted average: If readings represent values that persist for different lengths of time, a simple row average may not represent the period. You need to account for the duration associated with each reading.

For example, imagine one date has ten sensor readings and another has one. AVERAGEIFS gives the first date ten times as much influence because it includes ten rows. That may be exactly right for an observation-level average, but not if the question is “What was the average daily value?” The distinction is about the meaning of the data, not a defect in the formula.

Common errors and how to fix them

If Excel returns #DIV/0!, there may be no qualifying numeric values to average. The date limits could exclude every row, the date entries may be text, or the matching rows may have blanks or text instead of numbers in column B. Confirm the period, inspect a few date cells, and check that the corresponding values are numeric. A formula that silently substitutes zero can make a no-match result look like a real average, so diagnose the cause before choosing how to display it.

If the result seems too high or low, verify that the formula points to the intended columns and that the date and value rows remain aligned. Check for timestamps when using an end-date limit, and confirm that the start and end cells have the correct year as well as month and day. A date displayed without a year can conceal a year that is not the one you expected.

Text-formatted dates are another frequent source of confusion. Changing the number format may not repair them. Depending on the source and locale, you may need to convert the text into real dates, for example by using Excel’s date conversion tools or rebuilding the date with date components. After conversion, test whether the cells behave like dates by changing their display format or sorting them chronologically.

Useful variations and alternatives

If you want to add another condition, such as averaging only a particular product or category, add another criteria-range and criteria pair. With dates in A2:A100, values in B2:B100, categories in C2:C100, date limits in E2 and F2, and the category to match in G2, use =AVERAGEIFS(B2:B100,A2:A100,">="&E2,A2:A100,"<="&F2,C2:C100,G2). The result includes only rows that meet both date tests and the category test.

For a simple date range, AVERAGEIFS is usually clearer than building a longer array formula. You can also filter the table visually to inspect the matching records, but filtering alone does not calculate the average unless you add an appropriate summary. Keep the formula’s ranges bounded to the actual data where practical; using whole-column references can be convenient, but can make a workbook do more work than necessary when many formulas are involved.

If you need a message instead of an error when no values match, you can wrap the calculation. With the same date and value layout as the basic example, use =IFERROR(AVERAGEIFS(B2:B100,A2:A100,">="&E2,A2:A100,"<="&F2),"No matching numeric values"). This displays the message for an error, including a no-match error, but it can also hide other problems. Use it when a friendly display is useful, and keep a way to investigate unexpected results.

Frequently Asked Questions

Does AVERAGEIFS include the start and end dates?
Yes. Criteria using “greater than or equal to” for the start and “less than or equal to” for the end include records on both boundary dates, provided the date values match and the range does not have the timestamp issue described above.

How do I average values between two dates when the dates include times?
Use a start date with a greater-than-or-equal test and a less-than test against the day after the final date. For example, if the start and end dates are in E2 and F2, the timestamp-safe end criterion is "<"&F2+1.

Why does my formula return #DIV/0!?
Excel has no qualifying numeric cells to average. Check that the date range contains dates rather than text, the limits include the expected records, and the matching value cells contain numbers rather than blanks or text.

Does the formula average one result per day?
No. It averages every qualifying numeric row. If a day has more rows, those rows have more influence. Summarize by day first if each day should count equally.

Can I use AVERAGEIFS for dates that are not consecutive?
Yes, if you mean all records whose dates fall within the start and end boundaries; missing dates do not prevent the formula from working. If you instead want to include only a selected set of separate dates, add a method for those specific dates rather than treating them as one continuous interval.

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.