Excel Formula for Average Days Between Dates

Formulas & functions · Updated November 19, 2026

In Excel for Microsoft 365, if each row has a start date in column A and its matching end date in column B, use =AVERAGE(B2:B10-A2:A10) to calculate the average elapsed days. The result may include a decimal because Excel averages the exact date differences, including any time values.

What the formula calculates

Excel stores valid dates as serial numbers, so subtracting an earlier date from a later one gives the number of days between them. The formula subtracts each start date from the end date on the same row, then averages those differences. For instance, a difference of 10 means 10 elapsed days; it does not count both the start and end dates as separate days.

For a small example, suppose A2:A4 contains January 1, January 4, and January 10, while B2:B4 contains January 5, January 12, and January 15. The three elapsed periods are 4, 8, and 5 days. Their total is 17, and 17 divided by 3 is 5.6667 days, so the formula returns about 5.67. This is an average, not a rounded whole-day count.

The formula assumes the dates are valid Excel date values and that every end date belongs to the start date on the same row. A blank or mismatched row can distort a direct range calculation, so check the layout and missing data before relying on the result.

Set up the date columns

Use one row for each interval. Put start dates in A2:A10 and the corresponding end dates in B2:B10, with headings in row 1 if useful. Make sure each pair is on the same row: moving or sorting just one date column can pair unrelated records and produce a plausible-looking but incorrect average.

Enter dates as actual Excel dates, not text that merely looks like a date. A quick check is to select a date cell and temporarily choose Home > Number Format > General. A valid date normally displays as a number. Restore a date format afterward. If pasted dates remain text, use Data > Text to Columns to convert them when the text is consistently structured, or convert the source values before calculating.

Use a consistent convention for the records. If an interval starts and ends on the same date, subtraction returns zero elapsed days. That zero is a legitimate observation and lowers the average. If your business definition instead counts the start date as day one, you need to add one to each duration; do so only when that inclusive counting rule is genuinely intended.

Use a helper column for clarity

A helper column makes each row’s duration visible, which is useful for auditing and works well when you want to spot unusual intervals. With start dates in A2:A10, end dates in B2:B10, and the first duration in C2, enter:

=B2-A2

Fill C2 down through C10, then average the calculated durations. With the row differences in C2:C10, use:

=AVERAGE(C2:C10)

The first formula returns one duration per row; the second returns their arithmetic mean. In the January example, the helper cells would contain 4, 8, and 5, and the average is 5.6667. To fill down in Windows, select the formula cell and drag its fill handle, or copy and paste it into the remaining cells. On Mac, the same fill-handle method works; keyboard shortcuts can vary with keyboard settings.

This two-step method is easier to inspect than a single array calculation. It also makes it straightforward to format individual durations, filter records, or identify a negative result that indicates an end date earlier than its start date.

Handle blank or incomplete rows

A direct subtraction array should be used only when every included row contains both dates. If some rows are incomplete, a helper formula can leave the duration blank until both cells contain dates. With the start date in A2, the end date in B2, and the result in C2, enter:

=IF(COUNT(A2:B2)<2,"",B2-A2)

Copy that formula down the helper column, then average the helper results. With durations or blank strings in C2:C10, use:

=AVERAGE(C2:C10)

AVERAGE ignores text and empty cells, so incomplete rows left blank this way do not enter the calculation. A real zero-day duration remains numeric and is included. This distinction matters: excluding missing records is usually different from excluding records whose dates are the same.

Do not replace missing dates with zero or an arbitrary placeholder date. Those values can create enormous or negative durations and shift the mean. If incomplete records are important to report, count or flag them separately rather than quietly treating them as completed intervals.

Average gaps in one list of dates

Sometimes there is only one chronological list, such as successive service dates or delivery events, and the question is the average gap between each date and the next. That is different from averaging start-to-end intervals in paired columns. Put the dates in A2:A10 in chronological order; in Excel for Microsoft 365, this formula averages the seven adjacent gaps:

=AVERAGE(A3:A10-A2:A9)

For example, if the dates are January 1, January 4, January 10, and January 12, the gaps are 3, 6, and 2 days, whose average is 11 divided by 3, or about 3.67 days. The dates must be sorted in ascending order; otherwise a later-to-earlier transition creates a negative gap. To make the calculation easier to review, you can instead calculate each adjacent difference in a helper column and average those results.

Do not use the first-to-last span divided by the number of dates when you mean the average gap. With four dates there are three gaps, not four. For a simple, complete list, the mean adjacent gap equals the first-to-last elapsed span divided by the number of gaps, but calculating adjacent differences makes the observations explicit and helps expose irregular spacing or missing entries.

Choose elapsed days, calendar days, or workdays

Ordinary date subtraction measures elapsed calendar days. It includes Saturdays, Sundays, and holidays. It is suitable for questions such as how many days a process took, how far apart events occurred, or the average time between paired dates when every day counts.

If you mean business days, use Excel’s NETWORKDAYS function for each pair, then average the helper results. With the start date in A2, the end date in B2, and an optional holiday list in F2:F10, enter:

=NETWORKDAYS(A2,B2,$F$2:$F$10)

NETWORKDAYS counts working days from start through end, including both endpoints when they are working days, and can exclude listed holidays. That is an inclusive workday count, not the same measure as elapsed calendar days. Decide whether that convention matches your question before averaging. For example, a Monday-to-Tuesday interval returns 2 working days from NETWORKDAYS, while ordinary subtraction returns 1 elapsed day. If you do not need holidays, omit the third argument.

Excel also provides NETWORKDAYS.INTL when the weekend pattern differs from the usual Saturday-and-Sunday weekend. Choose it only when you need a custom weekend schedule, and verify that the selected weekend pattern matches the dates being analyzed. Workday definitions can vary by organization, so document the convention alongside the result.

Format and interpret the average

Date subtraction returns a number of days, but Excel may display the result using a date format inherited from a cell. If the answer appears as a calendar date, select the result cell and choose Home > Number Format > Number or General. Increase or decrease decimal places as needed. A value such as 5.6667 means five and two-thirds elapsed days, not a date in the year 1900.

Keep decimals when they are useful for comparing averages. If a report needs a whole number, decide whether to round to the nearest day, always round up, or always round down; those choices answer different questions. Formatting a result to show zero decimal places changes its display, not the stored value used in later calculations.

If the source dates include times, subtraction returns a fraction of a day as well. For example, a difference of 12 hours is 0.5. This may be desirable for turnaround-time analysis. If you intend to measure whole calendar-date changes only, remove or ignore time components in the source data rather than assuming the displayed date format removes them from the calculation.

Common errors and checks

  • Negative durations: Check whether an end date precedes its start date, or whether a row was paired incorrectly. Negative values may be valid in special cases, but they usually signal a data issue in elapsed-time reports.
  • A result that looks like a date: Change the result cell’s number format to Number or General. The calculation can be correct even when its display format is not useful.
  • Unexpectedly large values: Look for text dates, incorrect years, placeholder values, or a formula referencing the wrong columns. A date entered as text may not behave like a real date value.
  • A changed average after adding rows: Confirm that the formula’s ranges include the new records. A fixed reference such as B2:B10 does not automatically include data placed in row 11.
  • Different answers from inclusive counting: Subtraction measures elapsed time. Counting both endpoint dates as calendar days would add one for each interval under that specific convention. Do not mix inclusive counts and elapsed differences in the same average.

For a recurring dataset, an Excel Table can help formulas and references expand as rows are added. Select the data and choose Insert > Table, making sure the table has headers. A helper duration column in a table can fill its formula down automatically when new rows are entered, though you should still verify that new records contain both dates and follow the same counting rule.

Frequently Asked Questions

Can I average dates directly in Excel?
Yes, but average the differences if you want the average duration. Averaging end dates alone or start dates alone returns an average date, not an average number of days between paired dates.

Why does the average have a decimal?
The arithmetic mean need not be a whole number. An average of 5.5 days means the observed intervals average five and a half days. Change the number format to control how many decimals are shown, or use a rounding rule if a whole-day answer is required.

Does Excel count the start date and end date?
Subtraction returns elapsed days and does not count both endpoints inclusively. For two dates on consecutive days, the difference is 1. If your reporting rule counts both dates, add one per interval or use a suitable inclusive counting method, and apply that rule consistently.

How do I average days between dates while ignoring blanks?
Use a helper column that returns a blank until both dates are present, then average that column. AVERAGE ignores text and blank cells but includes numeric zeros, so same-day intervals remain part of the result.

How do I get the average number of working days?
Calculate each interval with NETWORKDAYS, optionally supplying a range of holiday dates, then average the resulting helper column. Remember that NETWORKDAYS counts working dates inclusively; confirm whether that matches your intended definition before comparing it with elapsed calendar days.

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.