If your dates are in A2:A10, enter =AVERAGE(A2:A10) in an empty cell, then format that cell as a date. Excel stores valid dates as numbers, so AVERAGE calculates their numerical midpoint; displaying the result as a date makes it readable. This is useful for finding an average delivery date, appointment date, or other typical date in a list.
The result is an arithmetic average, not necessarily one of the dates in your list. Before starting, make sure the cells contain real Excel dates rather than date-shaped text. The steps below are for Microsoft Excel for Microsoft 365 on Windows.
What You’ll Need
- A worksheet containing at least two dates, ideally in one column or row.
- An empty cell for the result.
- Dates Excel recognizes as dates. If you are unsure, select a date cell and check whether its number format can be changed to a date format.
You do not need an add-in or special analysis tool. The built-in AVERAGE function is enough for a straightforward list.
Step-by-Step
- Check the date list. Find the first and last cells in the set you want to average. For example, if the dates begin in A2 and end in A10, the range is A2:A10. Keep the header outside the range where possible. A text header is normally ignored by AVERAGE when it is part of a cell reference, but excluding it makes the formula easier to understand and reduces the chance of including the wrong data.
- Confirm that Excel recognizes the entries as dates. Click one of the cells and look at the Home tab’s Number Format box. If it shows a date format, that is a useful sign. You can also temporarily set the cell to General using Home > Number Format and see whether a number appears. Excel represents dates internally as serial numbers, so a serial number indicates that the entry is numeric; restore a date format afterward. Do not rely on how the cell looks alone, because text can be made to look like a date.
- Choose a result cell. Click an empty cell outside your list, such as B2. Avoid placing the result inside the source range: if the range includes the formula cell, Excel can report a circular reference or calculate an unintended result. If you want a clear label, type Average date in the cell beside the result.
- Enter the average formula. With dates in A2:A10 and the result in B2, type
=AVERAGE(A2:A10)and press Enter. If your dates occupy a different range, replace A2:A10 with that range. For a horizontal list, use its row range instead, such as A2:J2. The formula gives equal weight to each numeric date in the selected cells. - Format the answer as a date. Select the result cell, open Home > Number Format, and choose Short Date or Long Date. If the result displays a number such as 45671 rather than a calendar date, the calculation may already be correct; the cell is probably formatted as General or Number. Changing its format to a date changes its display, not the underlying average.
- Choose a suitable date display. Short Date is compact, while Long Date displays the weekday and month name. To choose a specific style, open the Format Cells dialog with Ctrl+1, select the Number tab, choose Date, and select a format. On Mac, use Command+1 to open Format Cells. The exact examples shown in the dialog can vary with regional settings.
- Check the result against the source dates. An average date should ordinarily fall between the earliest and latest included dates. For example, with dates in A2:A4 of January 1, January 10, and January 20, 2025, the formula
=AVERAGE(A2:A4)returns January 10, 2025. The dates are 9 days before and 10 days after that result, so their numerical average is January 10 at noon if the cells contain dates without times; the date-only format hides the noon time. If you need a date-only value, the displayed calendar date is still January 10.
In the example, each date contributes one number to the calculation. Excel does not give later dates more importance, and it does not find the most common date. If your question is which date occurs most often, that is a mode rather than an average and calls for a different calculation.
Common Mistakes
- Using dates stored as text. A value such as 4/5/2025 may look like a date but remain text, especially if it was imported or entered under a different regional convention. AVERAGE ignores text values in referenced cells, so the result can be based on fewer dates than expected. Convert the entries to real dates before averaging.
- Formatting only the source dates. The formula result has its own cell format. Making A2:A10 look like dates does not automatically format B2. Format the result cell itself.
- Including zero values unintentionally. A zero is numeric, not a blank, and AVERAGE includes it. In a date-formatted cell, zero may display as an old or unexpected date, depending on the workbook’s date system. Remove placeholder zeros or use a method designed to exclude them if they do not represent real dates.
- Including the wrong cells. A range that extends into a different dataset, contains an unintended date, or stops before the final entry changes the answer. Check the colored range outline while editing the formula.
- Expecting an average to match an entry. The answer can fall between two dates in the list. If date-and-time values are included, the average can also include a fractional day that represents a time.
Tips
- Use a table for a growing list. If you convert the data to an Excel table with Insert > Table, new rows can be included more conveniently as the table expands. Keep the result outside the source date column, and check the formula range when adding rows.
- Use a consistent date convention. Entries such as 03/04/2025 can mean March 4 or April 3 depending on regional settings. Use unambiguous month names when entering dates by hand, or confirm the source system’s convention before converting imported data.
- Decide how to handle time values. Excel stores a time as a fraction of a day. If the source cells include both dates and times, AVERAGE includes both; the result can therefore have a time component. A date-only display hides it, while a date-and-time format reveals it.
- Keep the formula readable. Put a label next to the result and, if the workbook has multiple lists, label which group the average describes. That prevents a valid calculation from being mistaken for an average of a different set.
Troubleshooting
The formula shows a serial number. Select the formula cell and choose Home > Number Format > Short Date. If you prefer another display, use Format Cells and select a date style. Do not replace the formula merely because the result initially appears as a number.
The result is unexpected or seems too early. Check whether any cells contain zero, whether the selected range includes unrelated values, and whether some date-looking entries are text. AVERAGE ignores text in referenced cells but includes numeric zeros. Compare the formula’s highlighted range with the intended list, and inspect questionable cells by changing their format to General.
The formula returns #DIV/0!. This usually means there are no numeric values for AVERAGE to calculate. Confirm that the range contains recognized Excel dates and is not empty or made up only of text. If the dates were pasted from another application, convert them to real date values, then recalculate.
The formula returns #VALUE! or another error. Look for an error value inside the selected range, such as #N/A, or check that the formula was entered correctly and that the parentheses are balanced. A range reference uses a colon between its first and last cell, as in A2:A10. If your workbook uses a different list separator for function arguments, follow the separator Excel inserts; this single-range AVERAGE example does not require multiple arguments.
The date is off by a day or the result shows a time. Check whether the source cells include times, and inspect the result using a date-and-time format. A fractional part is a portion of a day, not an error. Also verify that imported dates were interpreted in the intended month/day order. Changing number formatting cannot fix a value that Excel interpreted as the wrong date.
Frequently Asked Questions
Does AVERAGE work directly with dates?
Yes. Excel stores recognized dates as serial numbers, and AVERAGE can average those numbers. Format the result cell as a date so the answer displays as a calendar date.
Does Excel include blank cells?
No. AVERAGE ignores blank cells in a referenced range. It does include numeric zeros, so a cell containing 0 is not treated like an empty cell.
What happens if some dates are text?
Text values in referenced cells are ignored by AVERAGE. That can produce an average based on only part of your list. Convert text dates into genuine Excel date values and check that the count of included dates matches what you expect.
Can the average date include a time?
Yes. Dates with times contain fractional-day values, and averaging them can produce a time component. Use a date-and-time number format to see it, or a date-only format if you only need the calendar date.
How do I average dates in two separate ranges?
For a simple calculation, place the dates in one continuous range if practical, then use AVERAGE on that range. If the dates are in separate blocks, enter each block as an additional argument to AVERAGE, using the separators Excel expects in your regional settings. Check that no unintended cells are included.
Is the average date the same as the midpoint between the first and last dates?
Not always. AVERAGE gives every date in the list equal weight. The midpoint between the earliest and latest dates uses only those two dates, so other values in the list can make the overall average different.
Related
- How to Enable the Text Import Wizard in Excel for CSV Files
- How to Enable Track Changes in Excel
- Enable Scroll Lock in Excel: Keyboard and On-Screen Methods
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Minimum Sample Size Formula: How to Calculate ItThe minimum sample size depends on what you are estimating or testing. For a proportion in a large…
- How to Get a Drop-Down Filter in ExcelTo get drop-down filter arrows in Excel, select a cell in your data range, then choose Data >…
- Vlookup with multiple criteria?Learn how to use VLOOKUP with multiple criteria in Excel, build a reliable helper key, avoid common errors,…
- Excel: Sum VLOOKUP Values Across Multiple Matching RowsTo total every row that matches a lookup value, use SUMIF, not a regular VLOOKUP: with the matching…
- How to Match Two Excel Sheets with VLOOKUP in ExcelTo match records between two Excel sheets, put the value you want to find in the first column…
- Excel Sort by Name Not Responding? Fixes to TryIf Excel’s sort by name appears not to respond, first select a cell in the full data range…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.