Average Days Between Dates in Excel: Formulas & Tips

Formulas & functions · Updated November 19, 2026

If your dates are listed in chronological order in A2:A10, enter =AVERAGE(A3:A10-A2:A9) to calculate the average number of days between consecutive dates in Excel for Microsoft 365. For example, dates of January 1, January 4, January 10, and January 12 produce gaps of 3, 6, and 2 days, so the average is about 3.67 days.

What “average days between dates” means

The phrase can describe two different calculations. The most common is the average gap between consecutive dates in a list: subtract each date from the one immediately after it, then average those gaps. This is useful for measuring the typical time between orders, appointments, payments, service calls, or other events.

A second meaning is the average duration of several separate date pairs, such as the time from each project’s start date to its end date. That requires subtracting the start date from the matching end date on each row. The right formula depends on how the dates are arranged, so decide what one “interval” means before calculating.

Excel stores valid dates as serial numbers, with each whole-number step representing one calendar day. Subtracting an earlier date from a later date therefore gives the number of elapsed days. You usually do not need a special date-difference function for ordinary day counts.

Use one formula for consecutive dates

For a single column of dates, put the earliest date at the top and sort the list in ascending order. The formula in the opening example subtracts the first date from the second, the second from the third, and so on, then averages those differences. In Microsoft 365, Excel can evaluate the date ranges as arrays inside AVERAGE.

For a small example, suppose A2 is January 1, A3 is January 4, A4 is January 10, and A5 is January 12 of the same year. With those dates in A2:A5, enter =AVERAGE(A3:A5-A2:A4). The formula averages 3, 6, and 2, returning 3.6667 days. The value is an average, not a date; you can display it as 3.67 by formatting the result as a number with two decimal places.

Use the longer A2:A10 version when the list contains nine dates: it calculates eight intervals. In general, a list of n chronologically ordered dates contains n−1 gaps. The first date is not itself an interval, which is why the two ranges in the formula begin and end on different rows.

Use a helper column to see each gap

A helper column is often easier to audit, especially when you want to check unusual intervals or explain the calculation to someone else. With dates in A2:A10, leave B2 empty and enter the following formula in B3, then fill it down through B10:

=A3-A2

Each cell in column B now shows the difference between one date and the date directly above it. With the resulting gaps in B3:B10, enter =AVERAGE(B3:B10) in another cell to calculate their mean. This two-step approach gives the same answer as the single array formula, provided the dates and intervals are the same.

To fill the formula down, select B3 and drag its fill handle to B10, or copy the cell and paste it into the remaining rows. Excel adjusts the row references automatically. If a gap looks wrong, inspect the two source dates on that row before trusting the average.

Average durations in paired start and end columns

If each row has its own start and end date, do not compare dates across neighboring rows. For example, assume start dates are in A2:A10 and the corresponding end dates are in B2:B10. With those paired ranges, use =AVERAGE(B2:B10-A2:A10) in Microsoft 365. It calculates each row’s end-minus-start duration and averages the results.

For a visible audit trail, add a duration column instead. With a start date in A2 and its matching end date in B2, enter =B2-A2 in C2 and copy it down. Then average the numeric duration results in column C. Check that each end date belongs to the start date on the same row; misaligned pairs can produce a plausible-looking but meaningless result.

These calculations count elapsed calendar days, so a start date of Monday and an end date of Tuesday gives one day. If the start and end are the same date, the result is zero. Some business processes count both the start and end dates as full days; that is a different counting convention and should be applied consistently.

Handle blank rows and incomplete records

Blank cells deserve attention because subtracting a genuinely empty cell can behave like subtracting zero, creating an interval you did not intend. If your consecutive-date list may contain gaps, use a helper column that returns an empty text value whenever either date is missing. With dates in A2:A10, enter this in B3 and fill down to B10:

=IF(OR(A3="",A2=""),"",A3-A2)

With the helper results in B3:B10, use =AVERAGE(B3:B10). AVERAGE ignores text values such as the empty text returned by the formula, so incomplete intervals are left out rather than treated as zero. Make sure the remaining dates are still in chronological order; skipping a blank row does not automatically mean the next listed event should be compared with the previous event you intended.

If blank dates mean an interval is unfinished and should not be included, this guarded helper-column method makes that rule explicit. If a blank indicates missing data that needs correction, investigate it instead of silently excluding it. An average can conceal missing records, so the treatment of blanks should match the purpose of the analysis.

Calendar days versus business days

Subtracting dates counts calendar days, including weekends and holidays. For elapsed working days, Excel’s NETWORKDAYS function can count weekdays and optionally exclude a holiday list. Its ordinary counting convention includes both the start and end dates when they are workdays.

For a forward interval that should count workdays after the start date through the end date, assume the start is in A2, the end is in B2, and holiday dates are in D2:D10. Use =NETWORKDAYS(A2+1,B2,$D$2:$D$10). For a whole set of intervals, calculate this value on each row in a helper column and average those results. Add the holiday range only if it contains actual Excel dates; leave it out if you do not need holiday exclusions.

The A2+1 adjustment avoids counting the start date as an elapsed workday. Confirm that this is the convention your report needs, particularly when intervals can start or end on weekends, holidays, or nonstandard workdays. For workweeks that are not Monday through Friday, Excel also provides NETWORKDAYS.INTL, which lets you specify a weekend pattern. Calendar-day and workday averages answer different questions and should not be compared as if they were interchangeable.

Read and format the result correctly

Subtracting dates returns a number of days, and averaging those numbers can produce a fraction. An answer of 3.67 means an average gap of 3.67 days, not three days and 67 hours. If you want to show hours as well, multiply the fractional part by 24: 0.67 of a day is about 16.08 hours. Keep the underlying result numeric so it remains useful in later calculations.

If Excel displays a calendar date instead of a day count, the result cell may be formatted as a date. Select the result, then choose Home > Number Format and select General or Number. Increase or decrease the displayed decimal places as needed. Formatting changes how the value appears, not the calculation itself.

For elapsed time that can exceed 24 hours, a time-style display may be useful, but ordinary date subtraction still returns days. For example, an interval of 1.5 days is one and a half days, equivalent to 36 hours. Choose a display that makes the unit clear so readers do not mistake a duration for a date on the calendar.

Common errors and how to check them

  • Dates are stored as text: A value that looks like a date may not be recognized as one. If subtraction returns an error or an unexpected result, check whether Excel treats the entries as dates. Re-enter or convert them to real date values, and verify how ambiguous day/month formats were interpreted.
  • Dates are not sorted: A consecutive-date formula uses row order. If the list jumps backward in time, some gaps will be negative. Sort the dates ascending if row order is not meaningful, or correct the source data before calculating.
  • The wrong rows are compared: For consecutive events, compare each date with the next one. For independent start-and-end pairs, compare dates on the same row. Mixing these layouts changes the question being answered.
  • Blanks or duplicates affect the result: Decide whether blank intervals should be omitted and whether repeated dates represent valid zero-day gaps. Do not remove duplicates automatically if multiple events really occurred on the same date.
  • The displayed result looks strange: Check the cell’s number format and the formula’s range boundaries. A date-formatted result can look like a calendar date, while accidentally including a heading or unrelated cell can distort an average.

Choose the method that fits the data

For a clean, sorted list of dates in Microsoft 365, the single AVERAGE formula is compact. Use a helper column when you want to inspect every gap, manage missing dates, or make the calculation easier to explain. For separate start and end dates, calculate one duration per row rather than comparing dates from different records. For work schedules, use a working-day function and state the counting convention clearly.

These methods also help answer a related question: how often does an event happen on average? The average gap is one useful summary, but it can be distorted by an unusually long pause or a cluster of events. If timing varies substantially, review the individual intervals alongside the average; a single mean does not describe every gap or guarantee that the next event will follow the same pattern.

Frequently Asked Questions

How do I calculate average days between dates in Excel?
For chronologically sorted dates in A2:A10, enter =AVERAGE(A3:A10-A2:A9) in Microsoft 365. It averages the differences between each adjacent pair of dates.

Does Excel count the start date when I subtract dates?
No. Subtracting the start date from the end date gives elapsed days. Dates one day apart return 1, while identical dates return 0. A rule that counts both endpoints is a different convention.

Can the average number of days be a decimal?
Yes. The mean of whole-day gaps can be fractional, such as 3.67 days. That is a valid average; format the result as a number to display the decimal clearly.

How do I exclude weekends and holidays?
Use NETWORKDAYS for a workday count and provide a range of holiday dates if needed. Decide whether the start date should count: NETWORKDAYS normally includes both endpoint dates when they are working days, so elapsed-workday calculations may need an adjusted start.

Why does my formula show a date instead of a number?
The result cell is likely formatted as a date. Change its number format to General or Number using Home > Number Format. The formula result is a day count; the format controls its display.

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.