With dates in chronological order in A2:A6, calculate each gap in a helper column and average those gaps: in B3 enter =A3-A2, fill down to B6, then use =AVERAGE(B3:B6). For dates January 1, 4, 10, and 11, the gaps are 3, 6, and 1 days, so the average is 3.33 days.
Set up the dates and calculate the gaps
Use one column for the dates and another for the intervals between neighboring dates. The example uses five dates in A2:A6, which produces four gaps. In B3, subtract the earlier date from the later date. Fill the formula down through B6 so each row compares its date with the one directly above it. Then average the four results. The average is measured in days, and it can be a decimal.
For the example above, the gap values are 3, 6, 1, and—if the fifth date is included—whatever number of days separates that date from January 11. The opening example uses four dates, so its three intervals average to 3.33. To avoid mismatched examples, a four-date layout for that exact calculation is A2:A5, with gaps in B3:B5.
With four chronological dates in A2:A5, enter this in B3 and fill through B5: =A3-A2. With the resulting gaps in B3:B5, calculate the mean in another cell: =AVERAGE(B3:B5). If the dates are January 1, January 4, January 10, and January 11, the three gaps are 3, 6, and 1 days. Their total is 10 days; divided by three intervals, the average is 3.333 days.
Excel stores valid dates as serial numbers, so subtracting an earlier date from a later date returns the number of elapsed days. If a result looks like another date instead of a number, select the result cells and choose Home > Number Format, then select Number or General. Set decimal places if you want the average displayed as, for example, 3.33.
Decide what “between dates” means
The usual interpretation is the average of the gaps between each date and the next date in time order. This measures the typical interval in a sequence, such as days between service visits, purchases, project milestones, or meter readings. It is not the same as averaging every possible pair of dates. With four dates, there are three consecutive gaps but six possible date pairs; those calculations answer different questions.
Another possible question is, “How many days apart are the first and last dates, on average per interval?” For a chronologically sorted list with no missing dates, this gives the same result as averaging all adjacent gaps: the individual gaps add up to the total span. But the helper-column method makes each interval visible, which is useful for spotting unusual gaps and checking your work.
Also decide whether you want elapsed days or inclusive calendar-day counting. Subtraction measures elapsed time: January 1 to January 2 is one day. A report that counts both the starting and ending dates as included days would count that span as two calendar dates. That inclusive convention is a different measure, so add one to each gap only if the reporting rule specifically requires it.
Use a one-cell formula in Microsoft 365
If you do not need to inspect each interval separately, Microsoft 365 for Windows can average the adjacent differences in one formula. With chronologically ordered dates in A2:A5, enter =AVERAGE(A3:A5-A2:A4). Excel evaluates the range subtraction as an array of three gaps and averages the results. For January 1, 4, 10, and 11, it returns approximately 3.33.
This compact formula depends on the two ranges being the same size and aligned as neighboring rows: A3:A5 contains the later dates, and A2:A4 contains their preceding dates. It is convenient for a clean, continuous list, but the helper-column approach is easier to audit and troubleshoot. If you use an Excel release without dynamic array calculations, use the helper-column method instead.
For a simple sorted list with no internal blanks, another option calculates the total date span divided by the number of intervals. With dates in A2:A5, enter =(A5-A2)/(COUNT(A2:A5)-1). The example spans 10 days across three intervals, so it returns 3.333. This shortcut does not show individual gaps, and it needs at least two numeric dates; with fewer dates, its denominator becomes zero or negative.
Sort dates and handle duplicates
The adjacent-gap method assumes the dates are in chronological order. If they are not, a subtraction can produce a negative interval even when every date is valid. Select the full table, not just the date column, and use Data > Sort to sort by the date field from oldest to newest. Sorting the whole table keeps each date matched to its associated name, transaction, or other record.
Repeated dates are not automatically an error. Two events recorded on the same date have a gap of zero days, and that zero will lower the average. Keep duplicates when each row represents a separate event and same-day events should count. Remove or consolidate them only when the goal is the average interval between distinct dates. Check the business definition before deduplicating, because multiple legitimate transactions can occur on one day.
If equal dates appear because one value includes a time and another does not, the underlying values may not actually be equal. Excel date-times include a fractional part for the time of day. A difference can therefore be less than one day even when the displayed dates look the same. Decide whether the analysis concerns exact elapsed time or calendar dates before changing the values.
Blanks, text dates, and invalid values
A blank row inside a date list interrupts the sequence. If you fill a simple subtraction formula across that row, Excel may treat the blank like zero and produce a misleading result. Remove empty rows from the list or keep the valid dates together before calculating. If the data must remain in place, use a helper formula that calculates a gap only when both neighboring cells contain numbers.
With dates in A2:A6, enter this in B3 and fill down to B6: =IF(COUNT(A2:A3)=2,A3-A2,""). It leaves the gap cell empty unless both adjacent cells contain numeric date values. The AVERAGE function ignores those text-empty results, so with the remaining valid gap cells in B3:B6, use =AVERAGE(B3:B6). This skips incomplete adjacent pairs; it does not bridge across a blank to compare the dates on either side.
Dates that look correct but are stored as text will not behave like date serial numbers. Common causes include importing a file with a different regional date order, such as day/month versus month/day, or including leading spaces. A quick clue is that subtraction returns an error or the cells do not respond to date formatting. Confirm the intended order, then convert the values into real Excel dates before calculating; blindly converting ambiguous text can silently reverse month and day.
Calendar days versus working days
Ordinary subtraction counts every calendar day, including weekends and holidays. That is right for elapsed time between birthdays, deliveries, or events when the calendar span matters. If you need the number of workdays in each interval, Excel’s NETWORKDAYS function counts weekdays and can exclude a holiday list. It counts both endpoints when they are working days, so the counting convention matters.
For a convention that excludes the starting date but includes the ending date, put chronological dates in A2:A5 and holidays, if any, in E2:E10. Enter this in B3 and fill through B5: =NETWORKDAYS(A2,A3,$E$2:$E$10)-1. For a Monday-to-Wednesday interval with no holiday, the function counts Monday, Tuesday, and Wednesday; subtracting one leaves two working days after Monday through Wednesday. If your report instead counts both endpoints, omit the minus one. For no holidays, the third argument can be left out: with dates in A2:A5, use =NETWORKDAYS(A2,A3)-1 in B3 and fill down.
NETWORKDAYS uses Saturday and Sunday as weekend days by default. If an organization has a different weekend pattern, NETWORKDAYS.INTL supports custom weekend settings; check its arguments and choose the pattern that matches the schedule. Do not compare a calendar-day average with a workday average as if they were interchangeable. State which calendar and weekend rules were used when sharing the result.
Time values, rounding, and display
If cells contain both date and time, subtracting them returns a fraction of a day as well as any whole days. For example, a difference of 12 hours is 0.5. Keep the decimal when precise elapsed time matters. If the question is strictly about whole calendar-date changes, remove the time component consistently before calculating; simply formatting a date-time to hide its time does not remove the underlying fraction.
Rounding is usually best applied to the displayed average rather than to every gap first. Rounding each interval can change the eventual mean, particularly when there are many short intervals. Use number formatting to show a chosen number of decimal places while retaining the full value for later calculations. If a downstream report requires a rounded value, apply a rounding function to the final average and document the required precision.
Check the result before relying on it
A quick validation catches most mistakes. Confirm that all date values are genuine dates, the records are sorted, and the number of gaps is one less than the number of dates in a continuous list. Compare the sum of the gaps with the difference between the final and first dates. If those totals do not match, a row may have been skipped, duplicated, or calculated out of order.
- Check for negative gaps; they often signal unsorted dates.
- Check for zeros; they may be legitimate same-day events or accidental duplicates.
- Check unexpectedly large gaps against the source records and the intended time period.
- Confirm whether blanks should break the sequence or whether the next valid date should be compared with the previous valid date.
- Label the result as calendar days, workdays, or inclusive counted days so readers know what the average means.
Frequently Asked Questions
How do I average the days between dates in Excel? Put dates in chronological order, subtract each date from the date above it in a helper column, and average the gap cells. For dates in A2:A5, calculate the three gaps in B3:B5, then use =AVERAGE(B3:B5). The result can include a fraction of a day.
Does Excel count the start and end date? Date subtraction returns elapsed days, not an inclusive count of named calendar dates. January 1 minus January 2 as an interval is one elapsed day. For an inclusive count of both endpoints, add one to the elapsed-day gap, if that is the rule your report requires.
Can I calculate the average without a helper column? Yes. In Microsoft 365, with sorted dates in A2:A5, =AVERAGE(A3:A5-A2:A4) averages the adjacent differences directly. A helper column is still useful when you need to review each gap or isolate a bad interval.
How do I average workdays between dates? Use NETWORKDAYS for each adjacent pair, decide whether to include the start date, and then average those workday counts. You can supply a holiday range to exclude holidays. State the endpoint convention, because NETWORKDAYS counts both endpoints when they are workdays.
Why is my average showing as a date? Excel may have applied a date format to the result cell. The calculation can still be correct; select the cell and change its number format to Number or General. For an average with fractional days, choose the number of decimal places you want to display.
Related
- Copy Paste in Excel: Keep Column Widths When Pasting
- Copy an Entire Excel Spreadsheet to a New File
- Copy and Paste X Times in Excel: Repeat Cells or Rows
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel COUNT Formula Examples: Numbers, Blanks, and TextIn Excel for Microsoft 365, use =COUNT(A2:A20) to count cells containing numbers, including dates and times. For other…
- Excel Data Table Cheat Sheet: What-If Analysis GuideAn Excel Data Table is a What-If Analysis tool that recalculates a formula for different values in one…
- Microsoft Excel SUMIF Examples and How to Use ThemIn Microsoft Excel, SUMIF adds values that meet one condition. For example, with categories in A2:A6 and amounts…
- Excel CONCATENATE: Does It Keep Text Formatting?No. Excel’s CONCATENATE, CONCAT, and TEXTJOIN functions combine values into text, but they do not preserve rich-text styling…
- VLOOKUP Between Two Sheets: Examples and Common FixesTo use VLOOKUP across two Excel sheets, put the lookup value on one sheet and the data table…
- Excel WORKDAY Formula: Does It Exclude Weekends?Yes. In Microsoft Excel for Microsoft 365 on Windows, WORKDAY excludes Saturday and Sunday by default when it…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.