Excel Calculate Average Days Between Dates in a List

How-to basics · Updated November 19, 2026

To calculate the average number of days between consecutive dates in Excel, sort the dates from oldest to newest and average the differences between each neighboring pair. In Microsoft 365, if the dates are in A2:A6, enter =AVERAGE(A3:A6-A2:A5); for those five dates, the result is the average gap in days.

What the average between dates measures

This calculation finds the typical interval from one date in a sequence to the next. It is useful for measuring time between orders, appointments, service visits, payments, or other events recorded as dates. It is not the same as finding the average date, nor is it the same as averaging the length of separate start-and-end periods.

Excel stores dates as serial numbers, so subtracting an earlier date from a later one produces the elapsed time in days. For example, a difference of 5 means five days elapsed. When the dates include times, the result can include a fraction of a day: 0.5 is 12 hours. The AVERAGE function calculates the arithmetic mean of those differences, so a few unusually long gaps can pull the result upward.

Use a direct formula in Microsoft 365

Place one date per cell in a single column, with no headings or other text within the selected date range. Sort the dates in ascending order before calculating the gaps. With dates in A2:A6, enter this formula in an empty cell:

With the five consecutive dates in A2:A6, enter =AVERAGE(A3:A6-A2:A5). The formula returns one average gap, in days.

The two ranges line up pair by pair: A3 minus A2, A4 minus A3, A5 minus A4, and A6 minus A5. Excel averages those four differences. This compact range formula uses array arithmetic supported in Microsoft 365; it may not work the same way in older Excel versions without array-entry steps. The helper-column method below is a clear alternative and is also convenient when you want to inspect each interval.

The formula expects complete, valid dates in every cell from A2 through A6. It calculates the gaps in the order shown; it does not sort the dates for you. If your data is not already chronological, sort the entire data table by its date column so associated names or details remain on the correct rows.

Check the calculation with an example

Suppose A2:A6 contains January 1, January 5, January 14, January 20, and January 31 of the same year. The neighboring gaps are 4, 9, 6, and 11 days. Their total is 30 days across four intervals, so the average is 30 divided by 4, or 7.5 days. With those values in A2:A6, =AVERAGE(A3:A6-A2:A5) returns 7.5.

A result of 7.5 does not mean that an event happened at a date halfway between two entries. It means the mean elapsed interval is seven and a half days. The average can be a decimal even though every source date is a whole calendar date. You can keep the decimal to show the precision of the calculation or format the result to display a chosen number of decimal places.

Use a helper column to see each gap

A helper column makes it easier to verify the arithmetic, find an unusually large interval, or explain the result to someone reviewing the workbook. Keep the dates in A2:A6. In B3, calculate the difference between the date on that row and the date immediately above it, then fill the formula down through B6.

With dates in A2:A6, enter =A3-A2 in B3 and copy it through B6. The values in B3:B6 will be 4, 9, 6, and 11 for the example dates.

With the resulting gaps in B3:B6, enter =AVERAGE(B3:B6) in another empty cell. It returns 7.5 for the example. To copy the first formula down, select B3 and drag its fill handle to B6, or copy B3 and paste into B4:B6. Excel adjusts the relative cell references automatically, comparing each date with the one directly above it.

The helper approach also lets you sort or filter the gap values, apply number formatting, or inspect a particular interval without changing the average formula. Keep in mind that the top date has no preceding date in the list, so it correctly has no gap beside it. With n dates in a continuous sequence, there are n minus 1 neighboring intervals.

Prepare dates, blanks, and duplicate entries

Confirm that Excel recognizes the entries as dates rather than text. A quick check is to select a date cell and temporarily change its number format to General from Home > Number. A real Excel date appears as a serial number; a text entry may remain unchanged. You can also test a subtraction in an empty cell: if Excel reports a value error, one or both entries may be text or otherwise invalid.

For a clean average, decide what each row means before including it. A blank between dates is not a date and should not be treated as an event. The direct range formula can produce misleading results when blanks are included in the paired ranges, because subtracting a blank can behave like subtracting zero. Remove or correct incomplete rows, or use a helper column only for valid adjacent date pairs. Do not simply delete a row if its date represents a real event, since doing so changes which events count as consecutive.

Duplicate dates create a zero-day gap. That is mathematically valid when two events really occurred on the same date, and the zero should be included if each event belongs in the sequence. If duplicates are accidental repeated records, resolve them before calculating. Similarly, a negative interval usually signals that the dates are out of order; sort the list rather than averaging negative and positive differences together.

Choose elapsed days, calendar days, or business days

Subtracting dates measures elapsed calendar days. A Friday-to-Monday interval is three days, even though only one weekday separates those dates. This is usually right for elapsed-time questions such as how long customers wait between purchases or how many days pass between visits.

If you need weekdays only, first be precise about the question. A business-day count excludes weekends, and holidays may also need to be excluded. Excel’s NETWORKDAYS function can count working days between two dates, but the average-between-consecutive-dates formula shown above does not automatically become a business-day calculation. You would calculate each pair’s working-day count and then average those counts, with the holiday list and endpoint rules chosen to suit the task. Do not substitute a weekday count when the intended measure is ordinary elapsed days.

There is also an endpoint convention to consider. Date subtraction gives elapsed days: January 1 to January 2 is 1. If a report instead asks how many calendar dates are included counting both the start and end date, that example includes 2 dates. Inclusive counting is a different measure, so confirm the definition before adjusting any results.

Average separate durations instead of gaps

Some tables have a start date and an end date on every row. In that layout, the goal is often the average duration of each record, not the gap between one record and the next. For example, with start dates in C2:C5 and matching end dates in D2:D5, calculate each row’s duration in E2 and fill down through E5.

With start dates in C2:C5 and end dates in D2:D5, enter =D2-C2 in E2 and fill it down through E5. With those calculated durations in E2:E5, enter =AVERAGE(E2:E5) to average the individual periods.

This method pairs the start and end dates on the same row. By contrast, the consecutive-date method pairs each date with the date in the previous row. A common mistake is to use one method when the data is arranged for the other. Check whether each row represents a complete period or whether each row is an event in a chronological series before choosing a formula.

Format and troubleshoot the result

Excel may display an average as a decimal, a date, or a time depending on the output cell’s number format. If you see a calendar date instead of a number of days, select the result cell and choose Home > Number Format > General or Number. Set decimal places as needed. Applying a date format to a duration can make a correct numeric result look confusing.

If the output is unexpectedly large, check whether the range includes blank cells, text, a date that is far outside the usual sequence, or timestamps with times attached. If it is unexpectedly small, check for duplicate dates that create zero gaps or for an accidental extra row in the input range. Confirm that the dates are sorted oldest to newest and that the average includes precisely the intended intervals.

Microsoft 365 for Windows, Excel for Mac, and Excel for the web support ordinary date subtraction and AVERAGE. The menu names or appearance can vary by platform, but the formulas use the same comma-separated syntax shown here. The direct range formula relies on current array calculation behavior; if an older desktop version rejects it, use the helper-column method instead.

Frequently Asked Questions

How do I average days between dates in two cells? Subtract the earlier date from the later date to get the elapsed days. With a series of dates, calculate every neighboring difference and average those differences; a single pair has only one interval, so its average is simply that interval.

Does Excel count the start date as a day? No. Ordinary date subtraction returns elapsed days, so January 1 to January 2 is 1. If you need an inclusive count of calendar dates, define that separately because it gives 2 for the same example.

Why is my average a decimal? The arithmetic mean does not have to be a whole number. An answer such as 7.5 means an average interval of seven and a half days. It is not an error; choose the number format and decimal precision that make sense for your report.

Can I include dates that have times? Yes. Excel subtracts the date and time values together, so the gap may include a fraction of a day. If you want whole calendar-date differences regardless of time of day, remove or disregard the time component before calculating.

Should I sort dates before averaging the gaps? Yes, when you mean the time from each event to the next. Sort oldest to newest so every neighboring difference represents a chronological interval. Keep complete rows together when sorting a table, or the dates may become separated from their related records.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.