Excel Month to Date Average Formula

Formulas & functions · Updated November 22, 2026

The simplest Excel formula for a month-to-date average is =AVERAGEIFS(values, dates, ">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1), dates, "<="&TODAY()), assuming your dates are in one column and values in another. This returns the average of all values from the first day of the current month through today, automatically updating each day.

Understanding Month-to-Date Averages

A month-to-date (MTD) average is a rolling average that includes only data from the start of the current month up to the current date. It is commonly used in finance, sales, and operations to track daily performance against a monthly target. For example, if today is March 15, the MTD average includes March 1 through March 15 only, ignoring February and prior months.

This differs from a simple average of the entire month, which would be incomplete until the month ends. The MTD average is dynamic—it recalculates each day as new data is added, making it useful for dashboards and progress reports.

Setting Up Your Data for the Formula

Before using the formula, ensure your data is structured with a date column and a numeric value column. For instance, put dates in column A (A2:A100) and values in column B (B2:B100). The formula assumes dates are real Excel dates (not text), and values are numbers. If your dates are text, convert them using =DATEVALUE() or the Text to Columns wizard.

Here is the exact formula for that layout, entered in any empty cell:

=AVERAGEIFS(B2:B100, A2:A100, ">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1), A2:A100, "<="&TODAY())

This formula uses AVERAGEIFS with two criteria: dates greater than or equal to the first of the current month, and dates less than or equal to today. The DATE(YEAR(TODAY()),MONTH(TODAY()),1) part dynamically constructs the first day of the current month. The result updates automatically whenever the workbook recalculates.

How to Use a Dynamic Date Range

If you want to calculate the MTD average for a specific month other than the current one, replace TODAY() with a reference to a cell containing a date. For example, with a date in cell D1, use:

=AVERAGEIFS(B2:B100, A2:A100, ">="&DATE(YEAR(D1),MONTH(D1),1), A2:A100, "<="&EOMONTH(D1,0))

This uses EOMONTH(D1,0) to get the last day of that month, giving a full-month average. For a MTD average of a past month (e.g., if D1 is March 20, but you want only through March 20), replace the second criterion with "<="&D1.

Alternatives: SUMPRODUCT and Array Formulas

If your Excel version lacks AVERAGEIFS (pre-2007, unlikely but possible), use SUMPRODUCT:

=SUMPRODUCT((A2:A100>=DATE(YEAR(TODAY()),MONTH(TODAY()),1))*(A2:A100<=TODAY())*B2:B100)/SUMPRODUCT((A2:A100>=DATE(YEAR(TODAY()),MONTH(TODAY()),1))*(A2:A100<=TODAY()))

This divides the sum of values for the MTD period by the count of dates in that period. It works without array entry, but it is less efficient on large datasets. For Excel 365, you could use FILTER and AVERAGE:

=AVERAGE(FILTER(B2:B100, (A2:A100>=DATE(YEAR(TODAY()),MONTH(TODAY()),1))*(A2:A100<=TODAY())))

This is more readable and dynamic, but requires Microsoft 365 or Excel 2021.

Common Problems and How to Fix Them

  • #DIV/0! error: Occurs when there are no dates in the current month. Check that your date column has entries for the current month, or use IFERROR to return 0 or blank: =IFERROR(your_formula, 0).
  • Wrong results due to text dates: Ensure dates are real dates. Select the column, press Ctrl+H to find and replace, or use the DATEVALUE function.
  • Formula not updating: Excel recalculates automatically, but if calculation is set to manual (Formulas > Calculation Options > Automatic), press F9 to recalculate.
  • Including future dates: If your data contains future dates (e.g., planned entries), the <=TODAY() criterion excludes them, which is correct for MTD.
  • Blank cells in value column: AVERAGEIFS ignores blank cells, but if a date has a text value, it will be ignored too, potentially skewing the average. Clean your data.

Comparing MTD Average to Other Averages

It is helpful to distinguish MTD average from other common averages:

  • Full month average: Includes all days of the month, even future dates if present. Use =AVERAGEIFS(B2:B100, A2:A100, ">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1), A2:A100, "<="&EOMONTH(TODAY(),0)).
  • Running average (YTD): Year-to-date average includes all days from Jan 1 to today. Use =AVERAGEIFS(B2:B100, A2:A100, ">="&DATE(YEAR(TODAY()),1,1), A2:A100, "<="&TODAY()).
  • Simple average of all data: Use =AVERAGE(B2:B100) ignoring dates.

Choosing the right one depends on your reporting period. MTD is best for monthly targets, while YTD is for annual trends.

Using a PivotTable for MTD Averages

If you prefer a visual approach, create a PivotTable from your data. Drag the date field to Rows, the value field to Values, and set the value field to Average. Then group the date field by month (right-click > Group > Months). To limit to the current month, add a slicer or filter for the month. However, this method does not automatically stop at today—it averages the entire month unless you filter rows to today's date. To get MTD, you would need to add a helper column with a formula like =IF(A2<=TODAY(),1,0) and filter on that. The AVERAGEIFS formula is simpler for a single cell.

Frequently Asked Questions

How do I calculate MTD average for a different month?
Replace TODAY() in the formula with a cell reference containing a date from that month. For example, if D1 has March 15, use =AVERAGEIFS(B2:B100, A2:A100, ">="&DATE(YEAR(D1),MONTH(D1),1), A2:A100, "<="&D1) to get the MTD average through March 15.

Why does my formula return #VALUE! instead of a number?
This usually means your date column contains text that Excel cannot compare. Convert text to dates by selecting the column, going to Data > Text to Columns, and choosing Date. Or use =DATEVALUE() in a helper column.

Can I use this formula in Google Sheets?
Yes, AVERAGEIFS works identically in Google Sheets. The only difference is that Google Sheets uses semicolons in some regions, but the comma syntax works if your locale uses commas. The formula structure is the same.

How do I exclude weekends or holidays from the MTD average?
Add an additional criterion to AVERAGEIFS. For weekends, use =AVERAGEIFS(B2:B100, A2:A100, ">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1), A2:A100, "<="&TODAY(), A2:A100, "<>&"&"")—but that doesn't exclude weekends. Instead, use a helper column with =WEEKDAY(A2,2)<6 and add that as a criterion. For holidays, you need a list of holiday dates and use COUNTIFS to exclude them, which is more complex.

What if my data has multiple entries per day?
The AVERAGEIFS formula averages all values for each date, so multiple entries per day are included. If you want a daily average first, you would need to aggregate by day using a PivotTable or a formula like =AVERAGE(IF(...)) array formula.

Does the formula work with time-stamped dates?
Yes, as long as the dates include time, the comparison to DATE(...,1) and TODAY() still works because TODAY() returns midnight, and any time on today's date is greater than midnight, so it is included. For example, March 15 14:30 is <= TODAY() (which is March 15 00:00)? Actually, TODAY() returns the date with time 00:00, so March 15 14:30 is > TODAY(), so it would be excluded. To include all times today, use <=TODAY()+1 or <=NOW() as the upper bound. Use =AVERAGEIFS(B2:B100, A2:A100, ">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1), A2:A100, "<"&TODAY()+1).

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.