How to Average Dates in Excel

How-to basics · Updated November 20, 2026

To average dates in Excel, use =AVERAGE(A2:A10) when the cells A2:A10 contain valid Excel dates. Format the result as a date; if you need a whole calendar day rather than a date with a hidden fractional time, use =ROUND(AVERAGE(A2:A10),0).

What an average date means in Excel

Excel stores a date as a serial number: whole-number increments represent days, and the number’s display format makes it look like a calendar date. That means finding the average date is ordinary arithmetic. Excel averages the underlying day numbers, then displays the result according to the cell’s number format.

For example, imagine the dates in A2:A3 are January 1, 2026 and January 10, 2026. Their midpoint is January 5 at noon, because the dates are nine days apart. If the result is formatted to show only a date, Excel displays January 5; the fractional part, which represents noon, remains in the value. This is not an error—it is how an average can fall between two dates.

An average date can be useful for summarizing delivery dates, project milestones, customer response dates, or the typical date of a group of events. It is an arithmetic midpoint, not necessarily a date that appears in the source data.

Calculate an average date step by step

With dates in A2:A10, enter =AVERAGE(A2:A10) in an empty cell. This example assumes the cells contain real Excel dates, not text that merely looks like a date. The formula returns the arithmetic mean of the date serial numbers, including any time values attached to those dates.

  1. Enter or import the dates into one column, such as A2:A10.
  2. Click an empty cell where you want the answer.
  3. Type the formula =AVERAGE(A2:A10) and press Enter.
  4. Apply a date format if the result appears as a number.

For a concrete example, suppose A2:A4 contain January 1, January 10, and January 20, 2026. The average is January 10, 2026, because the three date serials average to the serial for that date. If instead A2:A3 contain January 1 and January 10, the average falls halfway between them: January 5 at noon. The different outcomes illustrate why an average date does not always land on a whole day.

Format the result so it looks like a date

If the formula returns a number such as 46027, Excel has calculated a value but is displaying its serial number rather than a calendar date. Select the result cell, then choose Home > Number Format dropdown > Short Date or Long Date. The number format changes how the value looks; it does not change the underlying calculation.

If you want to include the time as well as the date, choose a format that displays both. A date-only format can conceal a fractional day, so two results that look like the same date may have different times underneath. That distinction matters if you later compare the result with other date-time values or use it in another calculation.

In Excel for Microsoft 365 on Windows, those date-format choices are on the Home tab. Excel for Mac and Excel for the web also support the AVERAGE formula and date number formats; the ribbon layout can differ slightly. Changing the display format is separate from changing the formula.

Round to a whole calendar date

If your report should show the nearest day and should not retain a fraction of a day, use ROUND. With dates in A2:A10, enter =ROUND(AVERAGE(A2:A10),0). The formula first averages the dates, then rounds the resulting serial number to the nearest whole number. For the January 1 and January 10 example, the midpoint is January 5 at noon, and rounding to zero decimal places returns January 6, 2026.

That result may surprise someone expecting January 5, so choose the method based on what “average date” means for your task. Averages are numeric midpoints; rounding to the nearest day moves a midpoint at noon to the next date. If your convention is to discard the time portion and always use the earlier date, you can use =INT(AVERAGE(A2:A10)), assuming the dates are in A2:A10. For January 1 and January 10, that returns January 5, 2026. Format the result as a date to see it as a calendar value.

Average dates that meet a condition

Use AVERAGEIFS when you want the average date for records matching one or more conditions. Suppose column A contains region names and B2:B20 contains valid dates. With the region names in A2:A20 and the dates in B2:B20, enter =AVERAGEIFS(B2:B20,A2:A20,"North") to average dates for rows marked North.

For example, if the only North dates in the data are March 1 and March 11, 2026, the result is March 6, 2026. The formula averages values in B only where the corresponding cell in A matches North. Format the result as a date. If no rows match, or the matching date cells contain no numeric dates, AVERAGEIFS returns a division-by-zero error because there are no qualifying values to average.

You can also average dates within a time window. With dates in B2:B20, and with January 2 and January 10, 2026 as the only dates in that range, enter =AVERAGEIFS(B2:B20,B2:B20,">="&DATE(2026,1,1),B2:B20,"<"&DATE(2027,1,1)). It returns January 6, 2026, the average of those two dates. The inclusive start and exclusive end capture dates in 2026, including any times on December 31, without accidentally including January 1, 2027.

Blanks, text, and dates that look like dates

AVERAGE ignores blank cells and text in a referenced range, so empty cells in A2:A10 do not count as zero dates. Numeric zero values do count, however; a zero may display as a date near the beginning of Excel’s date system or as 0, depending on the cell format. Check for accidental zeros if the result looks much earlier than expected.

A common problem is imported dates stored as text. A cell can display something like 3/8/2026 and still be text rather than a numeric date. AVERAGE ignores text in a range, so those entries may be left out of the calculation. Date interpretation can also depend on regional settings: 3/8/2026 may be read as March 8 or August 3. Verify that the source data uses the intended month-day order before relying on the average.

  • Check whether a suspicious cell is treated as a number or date, rather than assuming its appearance proves it is a valid date.
  • Look for error values in the range; errors can cause an average formula to return an error.
  • Confirm that the formula refers to the intended cells and that date-only and date-time entries are being handled consistently.
  • Convert text dates to real dates using a method appropriate to the import and regional format, then check the converted values before averaging.

Excel workbooks can use different date systems, most commonly the 1900 system and, in some older or specially configured workbooks, the 1904 system. This can matter when copying dates between workbooks: the same serial number may represent a different calendar date. If copied dates shift by several years, check the date-system settings rather than trying to fix the average with a constant adjustment.

Average or median: which summary should you use?

The average is appropriate when you want the arithmetic midpoint of all included dates and unusually early or late values are meaningful parts of the summary. But a single very late date can pull the average later, even if most events occurred earlier. The median—the middle date after sorting—can better describe a typical case when the dates are skewed or contain outliers.

With dates in A2:A10, enter =MEDIAN(A2:A10) to return the median date. For example, if the dates are January 1, January 2, and January 30, 2026, the average is January 11 and the median is January 2. Both calculations are valid, but answer different questions: the average balances all date values, while the median identifies the middle observation. Format either result as a date.

Frequently Asked Questions

Can I average dates in Excel directly?
Yes. If the cells contain valid Excel dates, use AVERAGE on the date range. Excel averages their serial numbers. Format the answer as a date so it displays as a calendar value instead of a serial number.

Why does my average date show as a number?
The calculation may be correct, but the result cell is formatted as General or Number. Select it and choose Home > Number Format dropdown > Short Date or Long Date. The format changes the display, not the value.

Does AVERAGE count blank cells as zero?
No. AVERAGE ignores blanks and text in a referenced range. A cell containing the numeric value 0 is different: it is included and can pull the calculated date earlier.

How do I average dates for one category?
Use AVERAGEIFS, with the date range first and the category range and criterion after it. For example, if categories are in A2:A20 and dates are in B2:B20, =AVERAGEIFS(B2:B20,A2:A20,"North") averages the dates for North rows.

Why is my average date missing some entries?
Some apparent dates may be text, which AVERAGE ignores in a range. Check the imported data, regional date order, and formula range. Also check for error values, which can prevent the formula from returning a normal result.

Quick checklist for a reliable result

Before sharing an average date, verify that the input cells are real dates, the range includes the intended records, and any filters or category conditions match the question you are trying to answer. Decide whether a fractional day should remain, be rounded to the nearest day, or be discarded. Finally, format the result clearly and note whether you used an average or median if readers might interpret “typical date” differently.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.