To calculate an average date in Excel, enter valid dates in a range and use =AVERAGE(A2:A4), replacing the range with your cells. Format the result as a date: Excel averages the underlying date numbers, so the result may otherwise appear as a serial number or may include a hidden time.
How Excel calculates an average date
Excel stores dates as sequential numbers, with a whole number representing a day and a fractional part representing a time of day. The displayed date is a formatted view of that number. AVERAGE adds the numeric values in its arguments and divides by the count of those values. When the values are dates, that arithmetic produces the average point in time.
For example, with the dates January 1, January 10, and January 19, the first and last dates are 18 days apart. Their three dates are evenly spaced, so the average is January 10. More generally, the average is the date that balances the supplied date values numerically; it is not necessarily one of the dates in the list.
With dates in A2:A4, where A2 is January 1, A3 is January 10, and A4 is January 19 of the same year, enter =AVERAGE(A2:A4). The result is January 10. If the result cell is formatted as General, Excel may show a number such as 45301 rather than a calendar date. That number is not an error; it is the date serial value.
Prepare the date values before averaging
The cells need to contain dates Excel recognizes as numeric date values. A value that merely looks like a date can instead be text, particularly if it was imported from a file or entered in a format that conflicts with your regional settings. Text dates in a referenced range are ignored by AVERAGE, which can make the calculated result misleading.
Check a suspected value by selecting its cell and changing the number format temporarily to General. A recognized date will display as a number. You can also compare alignment as a clue: numbers and dates are normally right-aligned by default, while text is normally left-aligned, although manual formatting can change alignment. If imported dates are text, convert them to actual dates before relying on the result.
Keep the input range focused on the date values. If you have headings or notes, place them outside the range or make sure the formula refers only to the date cells. Blank cells in a referenced range are ignored, but a numeric zero is counted. A zero is not a blank: it represents a very early date in Excel’s date system and can pull the average far away from the dates you expect.
Format the result as a calendar date
After entering the formula, select the result cell and choose a date format. In Excel for Microsoft 365 on Windows, use Home > Number Format and select a date format, or open the Format Cells dialog with Ctrl+1 and choose a date category. On a Mac, Command+1 opens Format Cells; the formula itself works the same way. In Excel for the web, select the cell and use the number-format controls on the Home tab.
Choose a format that suits the context, such as a short date for a compact report or a format that spells out the month to avoid ambiguity between day and month. Formatting changes how the value appears; it does not change the underlying average. If a formula bar or a calculation displays a decimal fraction, that fraction may represent a time rather than an unwanted calculation error.
Understand times and rounding
Because dates can include times, the exact average may fall between midnight boundaries. For instance, if the dates in A2:A4 are January 1, January 10, and January 20 of the same year, their average is January 10 at 8:00 a.m. when the inputs are all midnight dates. A date-only format hides the time, so the cell appears to show January 10. Apply a date-and-time format if you need to inspect the full result.
If your reporting rule requires a whole date, decide how to handle a fractional day rather than assuming that Excel automatically rounds to the nearest midnight. With the same January 1, January 10, and January 20 values in A2:A4, =ROUND(AVERAGE(A2:A4),0) returns the nearest whole date serial, January 10. This rounds the numerical result; it does not change the original input dates.
Sometimes the requirement is to discard the time and keep the date at the start of the day. With the same dates in A2:A4, =INT(AVERAGE(A2:A4)) removes the fractional part of the average and returns January 10 at midnight. INT always rounds a positive number down to the preceding integer, so it is not interchangeable with rounding to the nearest day. Choose based on the rule your report needs.
Average dates that meet a condition
To average only dates associated with a category, use AVERAGEIFS. For example, suppose A2:A5 contains January 1, January 10, January 19, and January 28 of the same year, while B2:B5 contains Completed, Completed, Pending, and Completed. With dates in A2:A5 and matching status labels in B2:B5, enter =AVERAGEIFS(A2:A5,B2:B5,"Completed"). The three included dates are January 1, January 10, and January 28; their average is January 13.
AVERAGEIFS ignores rows that do not meet the condition and averages the qualifying numeric values. Make sure the date and criteria ranges cover matching rows and have the same dimensions. If no values meet the criteria, or if the matching cells contain no numeric dates, Excel returns a division-by-zero error. Check the spelling of labels and confirm that the qualifying dates are real date values.
Average date versus midpoint date
A common related question is whether the average date is the same as the midpoint between the earliest and latest dates. It is only the same in particular datasets. The average uses every included date, while a midpoint based on MIN and MAX uses only the two endpoints. If most observations cluster near one end, the average can differ substantially from the endpoint midpoint.
For example, if A2:A4 contains January 1, January 2, and January 19, the average of all three dates is January 7 8:00 a.m.; the midpoint between the earliest and latest dates is January 10. With those dates in A2:A4, =(MIN(A2:A4)+MAX(A2:A4))/2 returns the endpoint midpoint, January 10 at noon. Format the result to show time if that distinction matters. Use AVERAGE when every observation should contribute, and use the MIN/MAX midpoint only when the question is specifically about the halfway point between the extremes.
Common problems and checks
- The result is a large number: Apply a date format. Excel is displaying the date serial, not a failed calculation.
- The result looks unexpectedly early: Look for zeros or other numeric values in the range. Blanks are ignored, but zero is included.
- The average seems to exclude some dates: Check whether those cells contain text rather than numeric dates. AVERAGE ignores text in cell references.
- The displayed result seems off by part of a day: The average may include a fractional day. Display the time or apply a deliberate rounding rule.
- The result changes after editing the range: Confirm that the formula covers all intended rows and does not include unrelated numbers.
- Dates seem shifted between workbooks: Excel workbooks can use different date systems. The 1900 and 1904 systems assign different serial values to the same calendar date; avoid copying raw serials between workbooks without checking the displayed dates.
Also consider whether an arithmetic average is meaningful for the question. Averages can land on weekends or holidays, and they do not automatically move to a working day. If you need an average business date, define how nonworking days should be treated and apply that rule separately. Likewise, if records should have different importance, an unweighted AVERAGE gives each included date equal weight.
Frequently Asked Questions
Can Excel calculate the average date from a column?
Yes. If valid dates are in A2:A100, enter =AVERAGE(A2:A100) in another cell and format that result as a date. Adjust the range to include the intended records.
Why does AVERAGE return a number instead of a date?
Excel stores dates as numbers. The calculation can be correct even when the result cell is formatted as General or Number. Change the result cell’s number format to a date to display it as a calendar value.
Does AVERAGE include blank cells?
No. AVERAGE ignores truly blank cells and text in referenced cells, but it includes numeric zero values. A cell containing a formula that returns an empty string is text, not a truly blank cell, and is not averaged as a number.
Can I average dates from different years?
Yes. Excel’s date serials represent dates across years, so the same AVERAGE calculation works across a multiyear range. Confirm that each entry is a valid date and that the resulting date format displays the year when needed.
How do I get the average date for one group only?
Use AVERAGEIFS with the date range as its first argument and the relevant category range and criterion as the following arguments. Ensure each range spans corresponding rows, and check that matching date cells are numeric dates rather than text.
Related
- Excel Shortcut to Insert a Row on Windows and Mac
- Excel Data Analysis: Skills, Tools, and Limits
- Excel for Literature Analysis: What It Can and Can’t Do
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Insert a Checkbox in Word (Clickable or Printable)To insert a checkbox in Word, use Developer > Check Box Content Control for a box people can…
- How to Add Clickable Checkboxes in Excel: Step by StepTo add a clickable checkbox in current Microsoft 365 for Windows, select the cell or cells where you…
- Merge Cells in Excel Without Merging: Center AcrossTo make a heading look centered across several Excel columns without combining the cells, select the range, open…
- How to Delete a Second Blank Page in Microsoft WordTo delete a second blank page in Word, turn on formatting marks with Home > ¶ Show/Hide, find…
- Mac Excel Insert Row Shortcut: The Keys and How to Use ThemIn Excel for Mac, select the row where the new row should go, then press Control+Shift+= (the plus…
- Excel Text on a New Line in the Same Cell: A GuideYes. In Excel for Microsoft 365 on Windows, edit the cell, place the cursor where the next line…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.