To calculate the average gap between consecutive dates in Excel for Microsoft 365, enter the dates in A2:A10 in chronological order and use =AVERAGE(A3:A10-A2:A9). The result is the average number of days between each neighboring pair; for a more transparent setup, calculate each gap in a helper column and average that column.
Decide which dates you are comparing
“Average time between dates” can mean two different things. You may have one ordered list of events and want the average interval from one event to the next. Or you may have a start date and an end date for each separate case, project, or appointment, and want the average duration of those cases. Choose the calculation that matches your data before entering a formula.
- One date list: compare each date with the date immediately before it. A list of five dates produces four intervals.
- Start and end date pairs: subtract each start date from its matching end date, then average those durations.
Excel stores valid dates as serial numbers, so subtracting an earlier date from a later one returns elapsed days. For example, January 5 minus January 1 is 4 days. The same arithmetic works across months and years; Excel accounts for the different month lengths and leap days. The result is a duration, not another calendar date.
Average gaps in a single chronological list
For the direct formula in the opening paragraph, the date list must be in A2:A10, with the oldest date at the top and the newest at the bottom. The ranges deliberately start one row apart: A3:A10 contains the later dates and A2:A9 contains the preceding dates. Excel subtracts the two corresponding arrays and AVERAGE calculates the mean of the resulting gaps. This array calculation works in Microsoft 365.
For a small example, put January 1, January 5, January 12, and January 20 in A2:A5. The three gaps are 4, 7, and 8 days, for a total of 19 days. There are three intervals, not four dates, so the average is 19 divided by 3, or approximately 6.33 days. With the dates in A2:A5, this formula returns that result:
=AVERAGE(A3:A5-A2:A4)
If you prefer to inspect every interval, use a helper column instead. With the same dates in A2:A5, enter this in B3 and fill it down through B5:
=A3-A2
B3:B5 will show 4, 7, and 8. With those interval results in B3:B5, average them using:
=AVERAGE(B3:B5)
The helper-column approach is often easier to audit, especially if the dates are irregular or the calculation needs review. It also makes it obvious which intervals are included. In older Excel versions that do not calculate the direct array expression as expected, the helper column is a dependable alternative.
Average durations from start and end date pairs
When each row has its own start and end date, do not compare one row with the next. Instead, subtract the start date from the end date in the same row. For instance, put start dates in A2:A4 and their matching end dates in B2:B4. If the pairs are January 1–January 4, January 2–January 8, and January 10–January 14, the durations are 3, 6, and 4 days. Their average is 13 divided by 3, or about 4.33 days.
With start dates in A2:A4 and corresponding end dates in B2:B4, enter this in C2 and fill down through C4:
=B2-A2
With the calculated durations in C2:C4, calculate their average with:
=AVERAGE(C2:C4)
This method makes the row-by-row relationship clear and helps you spot an end date that precedes its start date. In Microsoft 365, an array formula can also calculate the average directly from paired ranges, provided both ranges have the same number of rows:
With start dates in A2:A4 and end dates in B2:B4, use:
=AVERAGE(B2:B4-A2:A4)
For a report that someone else may need to check, the helper column is usually preferable even when the compact formula works. It exposes the individual durations and makes unusual values easier to investigate.
Handle blank rows and incomplete records
Blanks need attention because a direct subtraction across ranges can treat an empty cell like zero, creating a misleading gap from Excel’s date-number starting point. AVERAGE ignores blank cells, but that does not guarantee the subtraction that produced its inputs is appropriate. If your data has incomplete rows, use a helper formula that leaves the gap blank until both neighboring dates exist.
With dates in A2:A10 and the interval between the date in each row and the previous row calculated in column B, enter this in B3 and fill down:
=IF(OR(A2="",A3=""),"",A3-A2)
The formula returns an empty string when either date is missing. With interval results in B3:B10, use:
=AVERAGE(B3:B10)
AVERAGE ignores text values such as the empty strings returned by this formula, so incomplete intervals are not included. However, a missing date in the middle of the list means the intervals on either side of that blank are not calculated. Decide whether that is right for your analysis. If the goal is to compare each recorded event with the previous recorded event, first remove or filter out blank rows so the dates become adjacent.
Check date order, duplicates, and invalid entries
For a chronological list, sort the dates from oldest to newest before calculating consecutive gaps. If the dates are out of order, some subtractions will be negative, and the average will not represent the typical waiting time. On Windows, select the date range and use Data > Sort Oldest to Newest. On Mac, the Data tab also provides sorting commands, though the ribbon layout may look different. Sort complete records together rather than sorting only the date column, or you may detach dates from their associated details.
Duplicate dates create a zero-day interval. That may be correct when two events occurred on the same date, but it lowers the mean. If duplicates are accidental, correct them before calculating. If they represent distinct events, keep them and document that zero-length gaps are part of the measure. Similarly, a negative duration in a start/end table usually signals reversed dates or a data-entry error.
Dates imported from another system may look like dates but be stored as text. A quick clue is that subtracting them produces a #VALUE! error, though cell alignment alone is not conclusive because formatting can change it. Check that Excel recognizes the entries as dates, and use the appropriate date or regional interpretation when converting imported text. Avoid silently excluding errors: find and fix the source values so the average reflects the intended records.
Read and format the answer correctly
A date subtraction result is measured in days, and a decimal is meaningful. An average of 6.33 means six days plus roughly one-third of a day, or about eight hours. Keep the result as a number if you want to compare intervals or perform more calculations. Applying a calendar-date format to the result can make a duration look like a date in 1900; that is a display-format issue, not an incorrect subtraction.
To show the result as a decimal number, select the result cell and choose Home > Number Format, then select Number. You can use the increase or decrease decimal buttons on the Home tab to choose how many decimal places to display. Formatting to two decimal places changes only what is shown; it does not round the underlying value used in later calculations.
If your source values include both dates and times, Excel stores the time as a fraction of a day, so subtracting them returns days with a fractional part. For an elapsed-time result, select the result cell, press Ctrl+1 on Windows (Command+1 on Mac), choose a suitable custom time format such as [h]:mm, and confirm. Square brackets allow elapsed hours to exceed 24; a standard clock format can wrap after a full day. If the time component is irrelevant, format the displayed result as a number of days instead.
Calendar days, workdays, and months are different
Ordinary date subtraction measures calendar days, including weekends and holidays. That is generally the right measure for elapsed time between two calendar dates. A business process may instead define its duration in working days, in which case you need a workday-counting method and an explicit rule about whether the start day, end day, weekends, and holidays count. Do not label a calendar-day average as a workday average.
Months also do not have a fixed number of days. An average of 30 days is not automatically an average of one month, and converting an average number of days to months using a fixed divisor can mislead. If the requirement is expressed in months, define whether you mean whole calendar months, month anniversaries, or an elapsed-day approximation. For many reporting tasks, it is clearest to calculate and label the average in days, then explain the convention.
Another important choice is whether each interval should have equal weight. A simple average gives each gap or record equal weight. It is not the same as dividing the total elapsed time by the number of people, projects, or calendar months unless those groups and intervals are defined consistently. Identify what one observation represents so the final statistic answers the intended question.
Frequently Asked Questions
How do I average the number of days between dates in Excel?
For dates in A2:A10 in chronological order, calculate the neighboring gaps with a helper column and average those results, or use =AVERAGE(A3:A10-A2:A9) in Microsoft 365. There are always one fewer consecutive gaps than dates.
Can Excel calculate the average between two dates?
For one start date and one end date, subtraction gives the elapsed days, but one pair alone does not provide multiple durations to average. To calculate an average, use several pairs, subtract each start from its matching end, and average the resulting durations.
Why is my average showing as a date?
The formula likely returned a valid number of days, but the result cell has a date format. Change the cell format to Number using Home > Number Format. The underlying calculation may be correct even if the display looks like a date.
Does AVERAGE include blank cells?
AVERAGE ignores genuinely blank cells and text values in its referenced range. But if a formula subtracts a blank date before AVERAGE receives the result, that subtraction may create an unintended number. Use a helper formula that skips incomplete pairs, or clean the source data first.
How do I calculate the average between dates that include times?
Subtract the full date-and-time values; Excel includes the time as a fraction of a day. Format the result as a number of days for a decimal-day answer, or use an elapsed-hours format such as [h]:mm when you want to display total hours and minutes.
Related
- Excel Named Range Scope: Workbook vs. Worksheet Names
- Excel Named Range Manager: How to View and Edit Names
- Excel Named Range Greyed Out? Causes and Fixes to Try
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Reference Table in a Data Validation ListYes. In Excel for Microsoft 365 on Windows, convert the source values into an Excel Table, define a…
- Google Sheets IMPORTRANGE: Setup, Uses, and Common FixesGoogle Sheets’ IMPORTRANGE function brings a cell range from one spreadsheet into another and keeps the imported results…
- How to Schedule an Email in the New Outlook AppIn the new Outlook app, write your email, select the arrow beside Send, choose Schedule send, and pick…
- Dotted Line in Excel and Word: How to Add OneTo add a dotted line at the bottom of a cell or selected range in Excel, select it,…
- Excel Dropdown List Based on Cell Value: How to Set It UpYes. In Excel for Microsoft 365, make a dependent dropdown by using the value in one cell to…
- Excel Shortcut Cheat Sheet for Windows and MacAn Excel shortcut cheat sheet is a quick reference to keyboard commands for editing cells, moving around worksheets,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.