To average values between two dates in Excel, use =AVERAGEIFS(B2:B100,A2:A100,">="&D2,A2:A100,"<="&E2). This averages the numbers in B2:B100 whose corresponding dates in A2:A100 fall on or after the start date in D2 and on or before the end date in E2.
The basic AVERAGEIFS formula
AVERAGEIFS is designed to average values that meet one or more conditions. In this example, column A contains dates, column B contains the values to average, D2 contains the first date in the period, and E2 contains the last date. The two date conditions make the range inclusive: observations on either boundary are eligible.
For a concrete example, suppose A2:A5 contains January 1, January 2, January 3, and January 4, while B2:B5 contains 10, 20, 30, and 40. If D2 is January 2 and E2 is January 3, the formula averages 20 and 30, returning 25. The January 1 and January 4 values are outside the requested period.
Change the cell references to match your worksheet. The average range is the range holding the numbers; each criteria range is the range Excel checks to decide whether a row qualifies. Keep the ranges the same size and aligned row by row. If your data starts on row 5, for example, use corresponding ranges such as A5:A200 and B5:B200 rather than mixing different starting rows.
How Excel evaluates dates
Excel stores recognized dates as serial numbers, which lets it compare them chronologically. The date displayed as 1/2/2026 is stored as a number, even though the cell is formatted to look like a date. AVERAGEIFS compares those underlying values, not the visual date style. The same date can be displayed in different formats without changing which records qualify.
This depends on the cells containing real Excel dates. Text that merely looks like a date may not compare as expected. A date imported from another system can be stored as text, especially if its day-month order is ambiguous or its format differs from your regional settings. Changing the cell format alone does not convert text into a date; it only changes how existing numeric values are displayed.
To inspect a suspicious date, select its cell and check the formula bar and alignment. Dates are often right-aligned by default, while text is often left-aligned, though manual formatting can override that clue. You can also test a sample in a spare cell: if Excel recognizes it as a number, a numeric check will return TRUE. Correct imported text dates before relying on date comparisons.
Prepare the data and date inputs
Keep one record per row, with the date in one column and the value to average in another. Avoid merged cells and subtotal rows inside the data range, since they make it harder to select matching ranges and can lead to confusing results. Put the period’s start and end dates in separate cells so you can update the analysis without editing the formula.
- Format the date column and the start and end cells as dates. In Excel for Microsoft 365 on Windows, select the cells and use Home > Number Format > Short Date. The exact display format can vary by regional settings.
- Make sure the values to average are numbers, not number-like text. Currency symbols and commas displayed through cell formatting are fine; characters typed into the value itself may cause it to be treated as text.
- Check that the start date is not later than the end date. Reversing them normally produces no qualifying records, rather than a useful average.
- Use a range that covers all relevant records but does not include unrelated labels or totals. Excel Tables can make expanding data easier to manage as new rows are added.
The same formula and basic cell-entry process work in Microsoft 365 for Mac and Excel for the web. Menu placement and date display can differ slightly across platforms, but AVERAGEIFS is available in these versions.
Enter and update the formula
After arranging the columns and date inputs, select the cell where you want the result and enter the formula. In this layout, dates are in A2:A100, values are in B2:B100, and the start and end dates are in D2 and E2:
=AVERAGEIFS(B2:B100,A2:A100,">="&D2,A2:A100,"<="&E2)
Press Enter. The formula joins each comparison operator to a date cell using the ampersand. The greater-than-or-equal condition includes the start date; the less-than-or-equal condition includes the end date. When D2 or E2 changes, Excel recalculates the average automatically.
If you copy the formula to another location, consider whether the input references should move. For a dashboard where the date inputs must stay fixed, use absolute references for those cells. With dates in A2:A100, values in B2:B100, and fixed inputs in D2 and E2, the copyable version is =AVERAGEIFS(B2:B100,A2:A100,">="&$D$2,A2:A100,"<="&$E$2). The dollar signs lock the input references when the formula is copied; they do not change the calculation.
Include full days when records contain times
A frequent source of an unexpectedly low result is a timestamp in the date column. Excel stores a time as a fraction of a day, so a record at 3:00 p.m. on the end date is later than the date value for midnight at the start of that day. The inclusive formula with a less-than-or-equal end condition can therefore exclude most records from the final day.
For timestamp data, include the entire end date by using a condition that is strictly earlier than the next day. With timestamps in A2:A100, values in B2:B100, a start date in D2, and an end date in E2, use:
=AVERAGEIFS(B2:B100,A2:A100,">="&D2,A2:A100,"<"&(E2+1))
This includes times from midnight at the start date up to, but not including, midnight after the end date. It works for date-only values too, because those dates fall before the next day. This approach is usually safer than trying to invent an end-of-day time such as 11:59 p.m., which may not cover every possible stored time value.
Add another condition, such as department
AVERAGEIFS can apply conditions beyond the date period. For instance, a sales sheet might have dates in A2:A100, sales amounts in B2:B100, department names in C2:C100, start and end dates in D2 and E2, and the department to analyze in F2. With those ranges, this formula averages sales only for the chosen department and dates:
=AVERAGEIFS(B2:B100,A2:A100,">="&D2,A2:A100,"<="&E2,C2:C100,F2)
Each additional criteria range needs its own criterion, and its rows must line up with the average range. You can use this pattern for categories such as product, location, or salesperson. Be aware that text criteria generally need to match the stored text; an extra space in a department name can make a record fail to qualify.
Average a particular month
For a monthly average, it is convenient to put the first day of the month you want to analyze in D2. Then set the upper limit to the first day of the following month, using a strict less-than condition. With dates in A2:A100, values in B2:B100, and the target month’s first day in D2, use:
=AVERAGEIFS(B2:B100,A2:A100,">="&D2,A2:A100,"<"&EDATE(D2,1))
EDATE advances the supplied date by one month, so the formula includes dates from the first day of the selected month but excludes the first day of the next month. That boundary style also handles timestamps throughout the final day. Enter D2 as a genuine date; displaying it with a month-and-year format is optional and does not change its stored value.
What the average includes and excludes
AVERAGEIFS calculates the arithmetic mean of qualifying numeric cells: it adds those values and divides by their count. Blank cells in the average range are not treated as zero, and text in that range is ignored. A numeric zero, however, is a real value and is included. If a zero represents a missing measurement in your dataset, decide whether it should be corrected or excluded rather than assuming Excel will disregard it.
Rows qualify based on the criteria ranges. A date inside the period does not guarantee that its value affects the answer if the corresponding value cell is blank or text. Conversely, a row with a zero value can qualify and lower the average. If you need to exclude zero values, add a further condition on the value range; with the same date and value layout and start and end cells, a nonzero criterion can be added as B2:B100,"<>0" to the AVERAGEIFS arguments. Check whether excluding zero is appropriate for the meaning of your data.
AVERAGEIFS does not weight values by how long a period lasts. If the rows are daily totals, it gives each qualifying row equal weight, whether a day has many transactions or few. If the intended measure is an average per transaction, average the transaction-level records instead of daily totals. The right formula depends on what one row represents.
Common errors and practical checks
- #DIV/0!: No numeric values met all the conditions. Confirm the dates, date types, range alignment, and any extra criteria. This error can also occur when qualifying rows have blank or text values only.
- The last date seems missing: Check whether the date column contains times. If it does, use the next-day exclusive boundary shown above.
- The answer is unexpectedly low or high: Check for zeros, duplicate records, values stored as text, and rows outside the selected ranges. Confirm that the date inputs are the dates you intend and that the year is correct.
- The formula is rejected: Check commas and parentheses, and verify that the quotes around the comparison operators are straight double quotes. Some regional Excel settings use semicolons rather than commas as formula separators; use the separator configured in your installation if Excel requires it.
- New rows are omitted: A fixed range such as A2:A100 will not include records added below row 100. Expand the ranges or convert the source data into an Excel Table and use structured references.
When the result matters, validate it with a small filtered sample. Filter the date column to the same period, inspect the values that qualify, and compare their sum and count. That check can reveal a hidden timestamp, text date, or unexpected zero more quickly than repeatedly changing the formula.
AVERAGEIFS compared with other approaches
For a straightforward date-window average, AVERAGEIFS is usually the clearest choice: the conditions are visible in the formula, and adding a category condition is simple. AVERAGEIF handles one condition, but a date interval requires both a lower and an upper bound, so AVERAGEIFS fits the task directly.
Filtering the data manually and using AVERAGE can help inspect a one-off result, but it is easier to forget to update the filter or include the correct rows. A PivotTable is a useful alternative when you want to summarize many periods or categories at once. It can group date fields and show averages, though the source data and PivotTable may need refreshing after changes. For one changing date range, a formula is often quicker to maintain.
Frequently Asked Questions
Can Excel average values between two dates, including both dates?
Yes. Use a greater-than-or-equal condition for the start and a less-than-or-equal condition for the end. If the date cells contain times, use a strict less-than condition for the day after the end date so the full final day is included.
Why does AVERAGEIFS return #DIV/0!?
It returns this error when there are no qualifying numeric values to average. Check that your date cells are real dates, that the selected period contains records, and that matching value cells contain numbers rather than blanks or text.
Does AVERAGEIFS count blank cells or zero values?
Blank cells in the average range are ignored, while numeric zeros are included in the arithmetic mean. Whether to exclude zeros depends on whether they represent real measurements or placeholders for missing data.
How do I average dates for one month only?
Use the first day of the month as the lower bound and the first day of the following month as an exclusive upper bound. The EDATE example above builds that upper bound automatically and also accommodates timestamps on the month’s last day.
Can I use AVERAGEIFS in Excel for Mac or Excel for the web?
Yes. AVERAGEIFS is available in Microsoft 365 for Windows, Mac, and the web. The formula logic is the same, although regional settings may affect the argument separator and the date format shown in cells.
Related
- Excel: Make Rows Collapsible with Grouping and Outlines
- Excel Merge Two Cells With Text Without Losing Data
- Excel Lock Slicer in Place? Position and Protect It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- VLOOKUP Examples for Practice: Learn Excel Lookup SkillsFor a first VLOOKUP practice problem, put product IDs in A2:A6, prices in B2:B6, and the lookup ID…
- Excel IRR for Monthly Cash Flows: Formula and ExamplesFor equally spaced monthly cash flows in B2:B14, enter =IRR(B2:B14) to calculate the rate per month; the first…
- Excel Days Until End of Month: Formula and ExamplesWith no input cells needed, enter =EOMONTH(TODAY(),0)-TODAY() to calculate the number of days from today to the last…
- Excel LEN Function: Count Text Characters in CellsIn Excel for Microsoft 365, use the LEN function to count the characters in a cell: with text…
- Excel Date Difference: Formulas for Days, Months & YearsTo find the number of days between two dates in Excel, subtract the earlier date from the later…
- Extract Middle Text in Excel: Formulas and ExamplesTo extract text from the middle of a cell in Excel, use MID: with the source text in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.