Excel Formula for Average Time Between Dates in Days

Formulas & functions · Updated November 19, 2026

If your dates are in chronological order in A2:A10, use =IF(COUNT(A2:A10)<2,"",(MAX(A2:A10)-MIN(A2:A10))/(COUNT(A2:A10)-1)) to calculate the average gap in days. For example, dates on January 1, January 4, and January 10 have gaps of 3 and 6 days, so the average is 4.5 days.

The quickest formula and what it calculates

The formula divides the span from the earliest date to the latest by the number of intervals between the dates. With nine valid dates, there are eight gaps, not nine; in general, a list of n dates has n − 1 intervals. The COUNT check prevents a division-by-zero error and leaves the result blank when fewer than two numeric dates are present.

This shortcut is appropriate when the dates are sorted from oldest to newest and you want the average between successive dates. It also works if two dates are the same: that interval contributes zero days. It ignores blank cells when counting dates, so a blank row between two valid dates does not count as an extra date.

In Excel, dates are stored as serial numbers: whole-number differences represent days. The formula therefore returns a number of days, potentially including a fraction. It does not automatically return a label such as “days,” nor does it count only weekdays.

What “average time between dates” means

People use this phrase for a few different calculations. Usually, it means the average of the gaps between consecutive events: the interval from the first date to the second, then from the second to the third, and so on. The shortcut above produces that result for dates in chronological order because all the adjacent gaps add up to the total span.

It is not the same as averaging the date values themselves. The average date tells you the midpoint of the dates’ positions on the calendar; it does not tell you how long events typically take to recur. For recurring appointments, orders, service visits, or project milestones, focus on the gaps between dates.

For example, dates on January 1, January 2, and January 11 have gaps of 1 and 9 days. Their average interval is 5 days. The dates are clustered at the start and end of the span, so the average gap does not mean that every interval is close to five days. For irregular schedules, it can help to review the individual gaps as well as their average.

Calculate and inspect each gap in a helper column

A helper column is useful when you want to see each interval, check for outliers, or calculate a different statistic later. With dates in A2:A10 sorted oldest to newest, enter =A3-A2 in B3 and fill it down through B10. Each row subtracts the previous date from the current date. For January 1, January 4, and January 10, the first two results are 3 and 6.

To average those gaps, with the calculated intervals in B3:B10, use =AVERAGE(B3:B10). In that three-date example, if B3 and B4 contain 3 and 6, the average is 4.5 days. AVERAGE ignores blank cells, which can be convenient if only some of the helper rows contain a calculated gap. However, check that every intended interval has a result; missing formulas would otherwise be left out of the average.

The helper-column method makes it easy to spot a very long pause or an accidental out-of-order entry. It also makes the meaning of the calculation visible to colleagues who open the workbook. Keep the original dates unchanged, and consider labeling column B “Days between dates” so the units are clear.

Format the result as days, hours, or a readable duration

Excel stores one day as the number 1, so a result of 4.5 means four and a half days. If Excel displays a decimal and you want a whole number, choose a number format or round the calculation; changing the display format does not change the underlying value. For a readable decimal-day result, select the result cell and use Home > Number Format to choose Number and set the desired decimal places.

If you need a rounded result rather than merely a rounded display, with dates in A2:A10 in chronological order, use =IF(COUNT(A2:A10)<2,"",ROUND((MAX(A2:A10)-MIN(A2:A10))/(COUNT(A2:A10)-1),2)). For the January example, the result remains 4.5. ROUND changes the stored result to the specified number of decimal places, so use it only when that loss of precision is acceptable.

For durations shown as hours and minutes, format the result cell as a duration rather than as a clock time. On Windows, select the cell and press Ctrl+1 to open Format Cells, then choose Custom and enter [h]:mm. On Mac, Command+1 opens Format Cells; Excel for the web provides number-format choices on the Home tab, though available custom-format controls can differ. Square brackets let hours accumulate beyond 24, so a 36-hour duration displays as 36:00 rather than wrapping around to 12:00.

Include times when the source values contain them

Excel dates can include a time as a fractional part of a day. If A2 contains Monday at 9:00 AM and A3 contains Tuesday at 3:00 PM, subtracting the earlier value from the later one gives 1.25 days, or 30 hours. The average-gap methods work with date-and-time values too, provided the entries are in chronological order and the result is formatted to show the duration you want.

For example, with date-and-time entries in A2:A10, the same average-span formula returns the mean interval in days. To convert a result in days to hours, multiply it by 24. With the average result in C2, enter =C2*24 in D2; if C2 is 1.25, D2 returns 30. Format D2 as a number, not as a date, so Excel displays the number of hours.

Be clear about the precision of the source data. If the cells contain dates only, Excel treats each value as midnight, and the calculation cannot recover times that were never recorded. If some entries contain times and others do not, the implied midnight can create a gap that looks unexpectedly short or long.

Calendar days versus working days

The ordinary date subtraction counts calendar days, including weekends and holidays. That is usually right for questions such as how often an event occurs or how many days pass between deliveries. If you need business-day intervals, decide first what “working days between” means for your process. Excel’s NETWORKDAYS function counts workdays inclusively, so subtracting one from the result gives the workday count elapsed between a pair of dates under that convention.

With a start date in A2, an end date in A3, and holidays listed in D2:D10, use =NETWORKDAYS(A2,A3,$D$2:$D$10)-1 to calculate the workday interval for that pair. For example, Friday to Monday returns 1: Monday is the next workday, while Saturday and Sunday are excluded. Average those pairwise workday intervals in a helper column if that is the metric you need. The chosen weekend pattern and holiday list matter; a business may observe different days off from the standard Saturday-Sunday weekend.

Blanks, duplicates, and dates that are out of order

COUNT includes numeric date values and ignores empty cells and text. That means the quick formula can still work with blank rows in the selected range, as long as the dates themselves are real Excel dates and are in chronological order. A cell that looks like a date may actually contain text, especially after copying data from a website or importing a file. If COUNT returns fewer entries than expected, check the source cells rather than assuming the average formula is wrong.

Duplicate dates represent a zero-day gap when they are consecutive in the sorted list. Whether that is correct depends on the question. If repeated dates indicate separate events, keep them; if they are duplicate records that should count only once, remove or consolidate them before averaging. A duplicate can lower the average, so the choice changes the meaning of the result.

Do not apply the shortcut to unsorted dates if you mean elapsed time between successive events. Sort the date list from oldest to newest first, or use a helper column and inspect the order. The MAX-minus-MIN shortcut measures the full span and divides by the count of intervals; it cannot reveal a sequence that jumps backward and forward in the rows.

Common errors and practical checks

  • A #DIV/0! error: There may be fewer than two numeric dates, or the formula may not include the intended range. The IF check in the example formula returns a blank when COUNT is below two.
  • A result that looks like a date: Excel may have formatted the output as a date. Change the cell’s format to Number or a duration format; the calculation itself may be correct.
  • An unexpectedly small or negative gap: Check whether entries are out of order, include times, or contain a date typed as text. Sorting does not convert text dates into numeric dates, so verify that Excel recognizes each entry.
  • A result that differs from a manual average: Confirm the number of intervals. Three dates produce two gaps. Also check whether your manual calculation includes duplicate dates, blank entries, or weekends differently.

A useful review is to calculate a few row-by-row gaps and confirm that their sum equals the difference between the latest and earliest dates. For sorted dates, that equality is a simple check that the dates and interval range line up. If it fails, a helper formula may be missing, the values may not be sorted, or the range may not cover the same entries as the average.

Frequently Asked Questions

What is the formula for average days between dates in Excel?
For chronological dates in A2:A10, use =IF(COUNT(A2:A10)<2,"",(MAX(A2:A10)-MIN(A2:A10))/(COUNT(A2:A10)-1)). It returns the average interval in days and leaves the cell blank if fewer than two numeric dates are present.

Why do I divide by the number of dates minus one?
Intervals sit between dates. Two dates make one interval, and five dates make four. Dividing by the date count instead of the interval count would make the average too small.

Can I average dates in a list that has blank cells?
Yes, if the remaining entries are valid Excel dates and chronological. COUNT ignores blanks, and the span is divided by the number of valid dates minus one. Check that any missing date does not represent an interval you intended to include.

How do I show the average as hours and minutes?
Excel stores the answer as a fraction or multiple of a day. Format the result as a duration, such as [h]:mm, to display hours and minutes without wrapping at 24 hours. This is especially useful when the dates include times.

Does this formula count weekends?
Yes. Subtracting dates counts calendar days, including Saturdays, Sundays, and holidays. For business-day intervals, calculate each pair with NETWORKDAYS and average those interval results, using a holiday list if appropriate.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.