To calculate an average date in Excel, use =AVERAGE(A2:A10) with dates in A2:A10, then format the result as a date. Excel averages the underlying date values, so the answer is the arithmetic mean of the dates—not necessarily the midpoint between the earliest and latest dates.
What an average date means in Excel
Excel stores a date as a number representing a day in its date system. A time, if present, is stored as a fraction of a day. That means ordinary arithmetic functions such as AVERAGE can calculate a mean date: they average the numbers, and Excel can display the result as a calendar date when the cell is formatted appropriately.
The average date describes the center of a set of observations. For example, if a list records when orders were placed, the average date indicates the mean order date. It does not tell you the most common date, the median date, or the midpoint of the full period unless the data happens to make those values coincide. A few unusually early or late dates can pull the average toward them, just as outliers affect an average of prices or measurements.
Excel workbooks can use different date systems, and that matters most when copying dates between workbooks configured differently. Within one workbook, the dates should share the same system. For normal calculations in a single workbook, you can usually work with dates as you would with other numeric values.
Calculate an average date with AVERAGE
For dates in cells A2 through A10, enter this in an empty cell:
With the dates to average in A2:A10, use: =AVERAGE(A2:A10)
Press Enter. The formula returns a number; if the result appears as a serial value rather than a recognizable date, change the result cell’s number format to a date. The formula updates when the dates in the range change. You can also select individual cells or more than one range, but a continuous range is usually easier to check and maintain.
In Excel for Microsoft 365 on Windows, you can format the answer from Home > Number group > Number Format dropdown > Short Date. Choose Long Date if you prefer a display such as Wednesday, January 10, 2024. These choices affect how the result is displayed, not the underlying calculation. Excel for Mac has date formats in the Home tab’s Number controls as well; the labels or ribbon layout can vary slightly by version.
If the source dates are in a table, you can use the column’s structured reference in AVERAGE. For instance, a table column named OrderDate can be averaged using =AVERAGE(Table1[OrderDate]). This expands automatically as table rows are added, provided the dates are entered in that column.
A worked example with actual dates
Suppose A2 contains January 1, 2024, A3 contains January 10, 2024, and A4 contains January 19, 2024. With those dates in A2:A4, enter =AVERAGE(A2:A4). The result is January 10, 2024: the three dates are nine days apart in sequence, and the middle date is also their arithmetic mean.
The answer will not always be one of the dates in the list. If the cells contain January 1 and January 2, their mean falls halfway between the dates: January 1 at noon. A date-only format hides the time portion, so the displayed result may look like January 1 even though the stored value includes half a day. To see that detail, apply a format that shows both date and time.
Likewise, averaging January 1, January 2, and January 10 does not produce the midpoint between January 1 and January 10. The mean is January 4 and a third, or January 4 at 8:00 a.m. The later observation has more influence because every date in the list contributes equally to the arithmetic average.
Average dates that meet a condition
When you need the average date for a subset of records—such as orders from one region—use AVERAGEIFS. It averages dates only for rows whose criteria are met. Imagine column A contains region names and column B contains dates. With North in A2, A3, and A5 and dates January 2, January 6, and January 12, 2024, in B2, B3, and B5, the average for North is January 6.
With regions in A2:A5 and dates in B2:B5, use: =AVERAGEIFS(B2:B5,A2:A5,"North")
To limit the calculation to dates in 2024 as well, use a start date inclusive and the next year’s start date exclusive. With the same layout, use: =AVERAGEIFS(B2:B5,A2:A5,"North",B2:B5,">="&DATE(2024,1,1),B2:B5,"<"&DATE(2025,1,1))
For the example records above, both formulas return January 6, 2024, assuming no other qualifying records. The exclusive upper limit avoids problems with dates that also contain times on December 31. If no rows match the criteria, AVERAGEIFS returns a division-by-zero error because there is nothing to average.
Make the result display as a useful date
A correct average can look wrong if the result cell is formatted as General or Number. A serial value is not necessarily an error: it can be Excel’s numeric representation of the date. Select the result and choose a date format. If you need to show a time as well, choose a custom format that includes hours and minutes, such as a date followed by hours and minutes.
For a keyboard route in Windows, select the result cell and press Ctrl+1 to open Format Cells, then choose a date format. On Mac, Command+1 opens the formatting dialog in many Excel versions; if that shortcut is intercepted by the system or differs in your setup, use the number-format controls on the Home tab. Applying a format changes the appearance, not the value. Avoid wrapping the result in TEXT just to make it look like a date unless you specifically need text: text is less convenient for later date calculations.
Blanks, zeros, text, and dates with times
AVERAGE ignores blank cells and text in referenced cells, but it counts numeric zero. This distinction matters because a blank cell generally means “no observation,” while a zero may be a real value—or a mistaken substitute for a missing date. A zero included in a date range can pull the average far away from the expected period and may display as an old date or as a number, depending on the workbook and format.
Dates imported from another system sometimes look like dates but are stored as text. AVERAGE does not treat text entries in a referenced range as numeric dates, so they can be silently excluded from the calculation. Check a suspicious cell by changing its format or using a simple comparison in a spare cell; if necessary, convert the text to real Excel dates before averaging. Do not assume that a date-looking display guarantees Excel recognizes the value as a date.
If entries include times, the average includes them. For example, midnight and noon on the same day average to 6:00 a.m. If the time is irrelevant and you want whole calendar dates, remove the time component from a cleaned copy of the data before averaging. Preserve the original values if their time information could matter later.
Common problems and how to diagnose them
- The answer is a number. The calculation may be correct; apply a date format to the result cell.
- The answer looks unexpectedly early. Look for zero values, dates outside the intended period, or dates stored as numbers that do not represent the intended calendar dates.
- The answer ignores some entries. Check for text-formatted dates, spelling or import issues, and cells outside the formula’s range. Blank cells are ignored by AVERAGE.
- The answer includes a time you did not expect. A mean can fall between whole days, and source cells may contain hidden times. Use a date-and-time format to inspect the value.
- The formula returns an error. Check that the range contains at least one numeric date and that the cell references are valid. With criteria-based averaging, confirm that at least one row meets all criteria.
One useful check is to inspect the source values individually and compare the formula range with the intended records. Sorting the data by date can reveal a stray year or an outlier that is mathematically valid but not part of the question you meant to answer. Do not delete an unusual date automatically; first determine whether it is a genuine observation or a data-entry problem.
Average, median, and midpoint are different
Use the mean when every record should contribute equally and you want the arithmetic center. Use a median when you want the middle observation after sorting and want less sensitivity to unusually early or late dates. With dates in A2:A4, a median can be calculated with =MEDIAN(A2:A4). For the example January 1, January 10, and January 19, 2024, the median is January 10, the same as the average; unevenly spaced dates can produce different answers.
The midpoint between the earliest and latest dates is another distinct measure. With dates in A2:A10, use =(MIN(A2:A10)+MAX(A2:A10))/2. This gives equal importance to the two endpoints and ignores how many observations occurred near either end. If the goal is to describe when a typical event occurred, an average or median may be more relevant than the period midpoint. Choose based on the question, not simply on which result looks neat.
Frequently Asked Questions
What is the formula for the average date in Excel?
Use AVERAGE with the cells containing the dates, such as =AVERAGE(A2:A10). Format the formula cell as a date if Excel displays a serial number.
Does AVERAGE ignore blank dates?
Yes. AVERAGE ignores blank cells and text in a referenced range, but it counts numeric zero. Check whether missing dates were entered as zeros, since those can distort the result.
Why does my average date show a decimal or time?
The mean can fall between two calendar days, so its underlying value may include a fraction of a day. A date-only format hides the time; a date-and-time format reveals it.
Can I average dates for one category only?
Yes. Use AVERAGEIFS with the date range as its average range and the category range and value as criteria, for example =AVERAGEIFS(B2:B5,A2:A5,"North") when categories are in A2:A5 and dates are in B2:B5.
Why is Excel not including some dates?
They may be stored as text, outside the formula’s selected range, or excluded by an AVERAGEIFS condition. Convert text dates to real date values and verify the range and criteria.
Related
- Q-Q Plot in Excel: How to Make and Read One
- Protect Sheet vs. Protect Workbook in Excel: Key Differences
- Protect Excel Sheets Except for a Few Cells
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for File Path: Get the Workbook LocationIn Excel for Microsoft 365 on Windows, use =CELL("filename",A1) to return the saved workbook’s path, file name, and…
- Excel Average Days Between Dates: Formula and ExamplesTo average the number of days between paired dates in Excel, subtract each start date from its matching…
- COUNTIF Examples: Formulas for Common Spreadsheet TasksUse Excel’s COUNTIF function to count cells in one range that meet one condition: =COUNTIF(range,criteria). For example, it…
- COUNTIF Excel: How to Count Matches and Sum ValuesCOUNTIF in Excel counts cells that meet one condition; it does not add the values in those cells.…
- Microsoft Excel Date Today: TODAY Formula and Useful TipsTo show today’s date in Microsoft Excel for Microsoft 365 on Windows, enter =TODAY() in a cell and…
- COUNTIF Between Dates in Excel: Formula and ExamplesTo count dates between two boundaries in Excel, use COUNTIFS with a lower-bound and an upper-bound condition. With…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.