To calculate the average time between consecutive dates in Excel, subtract each date from the one below it, then average those differences. For dates in A2:A5, enter =A3-A2 in B3, fill it down to B5, and use =AVERAGE(B3:B5); the result is the average interval in days.
Calculate the average gap between consecutive dates
This method answers the usual meaning of average time between dates: the average elapsed time from one recorded date to the next. It works for appointments, orders, maintenance events, payments, visits, or any other sequence of dated events.
Suppose A2:A5 contains January 1, January 6, January 16, and January 25, 2025. The three gaps are 5, 10, and 9 days. Their total is 24 days, and there are three intervals, so the average is 8 days.
- With the dates in A2:A5, enter
=A3-A2in B3 and fill the formula down through B5. The cells in B3:B5 return 5, 10, and 9. - With those interval results in B3:B5, enter
=AVERAGE(B3:B5)in another cell. It returns 8, meaning the average gap is eight days.
Use the difference between each adjacent pair, rather than averaging the dates themselves. Excel stores dates as serial numbers, so subtracting an earlier date from a later one gives the elapsed number of days. The helper column also makes it easy to see which intervals are unusually long or short and to check the calculation.
What the average represents
The result is an average of intervals, not necessarily a typical interval. In the example, two gaps are close to a week, but one is 10 days; the arithmetic mean still gives 8 days. If a few unusually long gaps could distort the picture, consider also reviewing the individual intervals or calculating a median. The median is the middle interval after sorting the gaps, and can better represent a typical value in some datasets.
There are three dates in the example and therefore two intervals, not three. In general, a continuous list of n dates creates n−1 gaps. This matters when checking a hand calculation: divide the sum of the gaps by the number of intervals. Excel’s AVERAGE function does that division for the numeric results in the helper column.
If the dates are in chronological order, the sum of all consecutive gaps equals the difference between the last and first date. That means the average of those gaps also equals the total span divided by the number of intervals. This equivalence does not mean you should divide by the number of dates: doing so would make the answer too small.
Make sure Excel recognizes the dates
Excel can subtract genuine date values because it stores them as numbers. A cell that merely looks like a date may instead contain text, especially when data was copied from a website, imported from a system using a different regional date order, or entered with an unrecognized format. Text values may produce a #VALUE! error or fail to behave like dates.
To check a suspicious value, select the cell and inspect the formula bar and its number format. Changing a cell’s display format does not convert text into a date; it only changes how a numeric value is displayed. If Excel recognizes the entry as a date, subtracting the cells should produce a number of days. If it does not, convert or re-import the values as dates before calculating intervals. Be particularly careful with ambiguous entries such as 04/05/2025, which can mean different dates under different regional conventions.
Dates can also contain times. Excel stores the time as a fraction of a day, so subtracting date-time values returns days and fractions of days. A result of 1.5 represents 36 hours. This is useful when the actual time of an event matters, but it may surprise someone expecting whole calendar days.
Sort the list and decide how to treat duplicates
For gaps that represent elapsed time moving forward, sort the dates from oldest to newest before calculating. Select the date list and use Data > Sort Oldest to Newest. If there are other columns describing the same records, sort the full table or expand the selection when prompted so each date stays matched to its record.
An unsorted list can produce negative intervals. Those negative values are mathematically valid subtraction results, but they do not describe forward chronological gaps. Sorting is usually the right fix; do not replace negative results with positive values unless the intended measure is the absolute distance between dates regardless of order.
Repeated dates create zero-day gaps. Keep them if each row is a separate event and same-day events should count as zero elapsed time. Remove or consolidate duplicates only if they are duplicate records rather than genuine events. The choice changes the average, so it is a data-definition decision, not merely a formatting preference.
Handle blank rows carefully
The basic helper-column method assumes the dates are in a continuous list. A blank cell between two dates breaks the adjacent-pair calculation: the formulas on either side of the blank do not automatically connect the last nonblank date to the next nonblank date. If blank rows are accidental, remove them or sort and compact the list first.
If you want the helper column to stay visually blank whenever either date in an adjacent pair is missing, use this formula. With dates in A2:A20, enter it in B3 and fill down through the rows containing possible pairs:
=IF(OR(A3="",A2=""),"",A3-A2)
AVERAGE ignores the empty text returned by this formula, but it does not bridge across a blank row. For example, if A2 and A4 contain dates while A3 is blank, this setup skips both adjacent comparisons involving the blank; it does not calculate A4 minus A2. To measure gaps between successive nonblank dates, first create a continuous list of valid dates, then calculate the adjacent differences.
Show the answer in days, hours, or a readable format
By default, a difference or average of date values is usually displayed as a number of days. For a decimal answer such as 2.5, the interval is two and a half days, or 60 hours. If the cell instead displays a calendar date, change its number format to General or Number to see the underlying interval value.
To convert an average stored in days into hours, multiply it by 24. With the average interval in B6, enter =B6*24 in another cell; if B6 is 8, the result is 192 hours. This converts the unit but does not change the underlying dates.
For an interval that includes times and should display as elapsed hours and minutes, use a custom duration format such as [h]:mm. The square brackets let hours accumulate beyond 24 rather than restarting at zero each day. In Excel for Microsoft 365 on Windows, select the result cell and press Ctrl+1 to open Format Cells, choose Custom, and enter the format. On Mac, Command+1 opens Format Cells. A standard time format such as h:mm may show only the time-of-day portion and can be misleading for durations longer than a day.
Choose calendar days or working days
Simple date subtraction counts calendar days, including weekends and holidays. That is usually appropriate for elapsed time: a gap from Friday to Monday is three calendar days. If the question is instead how many working days separate two events, define the workweek and holiday rules first. Excel’s NETWORKDAYS function counts working days inclusively at both ends, so subtracting 1 gives the number of working days elapsed after the starting date through the ending date under that convention.
With the earlier and later dates in A2 and A3, respectively, enter =NETWORKDAYS(A2,A3)-1 in C3. For a Friday-to-Monday interval with no holidays, the result is 1 working day. Copy that calculation for each consecutive pair, then average the resulting working-day intervals. NETWORKDAYS uses a Saturday-and-Sunday weekend by default; its weekend argument can specify other weekend patterns, and an optional holiday range can exclude listed holidays.
Do not mix calendar-day intervals and working-day intervals in the same average. Also decide how to treat a date that falls on a weekend or holiday and whether the start date should count. The subtraction of 1 above establishes one clear convention, but a different business rule may require a different calculation.
Average two date columns instead of a date sequence
Sometimes “time between dates” means the duration of each record, with a start date in one column and an end date in another, rather than gaps between successive events in a single list. Those are different calculations. For example, if each row describes a project, appointment, or service period, subtract the start date from the end date on that same row, then average the row durations.
With start dates in A2:A20 and corresponding end dates in B2:B20, enter =B2-A2 in C2 and fill down. Then, with the durations in C2:C20, use =AVERAGE(C2:C20). Check whether the endpoints should be included: ordinary subtraction measures elapsed time, so a start and end date on the same day produces zero days. A rule that counts both calendar dates as service days may need an inclusive count instead.
Common errors and checks
- Unexpected negative numbers: confirm the list is sorted oldest to newest and that each formula references the intended adjacent rows.
- #VALUE!: check for dates stored as text or other non-date content in the cells being subtracted.
- Too-small average: verify that you averaged the interval cells and that the list contains no unintended blank or duplicate records.
- A date appears instead of a number: change the result’s number format to General or Number; the calculation may be correct but displayed in an unsuitable format.
- Unexpected decimals: fractional days can come from times included in the source values. Decide whether to keep that precision or use date-only values for calendar-day intervals.
A quick audit is to inspect the first and last dates, count the valid records, and compare the sum of the interval results with the overall span from first to last. For a sorted, continuous list with no excluded dates, those totals should agree. A discrepancy can reveal a missing row, an incorrect reference, or a gap that was not included.
Frequently Asked Questions
What is the formula for average days between dates in Excel?
Subtract each date from the next date in a helper column, then use AVERAGE on those differences. For dates in A2:A5, calculate A3−A2 through A5−A4, then average the three results. The answer is in days.
Do I divide by the number of dates or the number of gaps?
Divide by the number of gaps. A list of five dates has four consecutive intervals, so its average interval is the sum of those four gaps divided by four. AVERAGE on the helper cells handles this automatically.
Can Excel calculate the average without a helper column?
Yes, newer Excel versions can evaluate array calculations, but a helper column is easier to inspect and troubleshoot, and it works reliably across more spreadsheet setups. The helper approach also makes each individual gap visible.
How do I average time between dates in hours?
Excel date differences are measured in days. Multiply the average day value by 24 to convert it to hours, or apply a duration format such as [h]:mm when the dates include times and you want an hours-and-minutes display.
How do I ignore weekends when averaging dates?
Calculate each interval as a working-day count with NETWORKDAYS, choosing the weekend and holiday rules that fit the data, then average those counts. State whether the start date is counted; NETWORKDAYS includes both endpoints, so subtracting 1 gives elapsed workdays under the convention described above.
Why does my average change after sorting?
Sorting should not change the average of forward consecutive gaps for the same complete set of dates if you calculate all adjacent intervals in chronological order: their sum is the first-to-last span. If the result changes, check for missing values, duplicates, changed row pairings, or a formula range that did not include all intervals.
Related
- Excel PivotTable Tips for Clearer Reports
- Excel Not Responding and Unsaved? Recover Your Work
- Excel Insert Slicer: Why No Connections Are Found?
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel LEN Formula Examples: Count Text in CellsIn Excel for Microsoft 365, use =LEN(A2) to count the characters in cell A2, including spaces and punctuation.…
- Excel Average Ignoring Zeros: Formula and ExamplesTo calculate an average in Excel while ignoring zeros, use AVERAGEIF with the criterion “0″. If your values…
- When VLOOKUP Does Not Work: Common Causes and FixesVLOOKUP usually fails because its lookup value does not exactly match the first column of the selected range,…
- Excel Time Sheet Sample: Layout, Formulas, and TipsAn Excel time sheet sample is a worksheet for recording each workday’s start time, unpaid break, end time,…
- Advanced Excel HLOOKUP Formula: Examples and TipsFor an advanced Excel HLOOKUP, use =HLOOKUP(F1,$A$1:$D$4,2,FALSE) when the value to find is in F1, the lookup keys…
- Excel Sort Dates From Earliest to Latest: A Clear GuideTo sort dates in Excel from earliest to latest, select a cell in your date column, then choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.