Excel Average Days Between Dates: Formula and Examples

Formulas & functions · Updated November 19, 2026

To average the number of days between paired dates in Excel, subtract each start date from its matching end date, then average the differences. For example, if start dates are in A2:A10 and end dates are in B2:B10, you can calculate the row-by-row differences in column C and use =AVERAGE(C2:C10).

How Excel calculates days between dates

Excel stores dates as serial numbers, so subtracting one date from another returns the elapsed time in days. If a start date is January 1 and the end date is January 4, the subtraction returns 3: the time from the start of January 1 to the start of January 4 is three days. The same arithmetic works across months and years, including leap years.

For an average, the important step is matching each start date with the correct end date. Each row should represent one complete interval, such as an order and its delivery, a task and its completion, or a customer’s enrollment and cancellation. The average is the sum of those elapsed intervals divided by the number of valid intervals.

This is elapsed calendar time, not necessarily the number of dates counted inclusively. January 1 through January 4 has an elapsed difference of 3 days, while a count that includes both January 1 and January 4 would be 4 calendar dates. Decide which meaning your report needs before choosing a formula.

Arrange paired dates in columns

A clear table makes the calculation easier to check. Put one date pair on each row, with the start date in one column and the end date in another. Add a third column for the interval in days. For example:

  • A1: Start date; B1: End date; C1: Days elapsed.
  • Row 2: January 1, 2026, and January 4, 2026; the interval is 3 days.
  • Row 3: January 2, 2026, and January 8, 2026; the interval is 6 days.
  • Row 4: January 5, 2026, and January 8, 2026; the interval is 3 days.

The intervals total 12 days across three records, so their average is 4 days. In a real workbook, check that the dates are genuine Excel dates rather than text that only looks like a date. A quick clue is that subtracting valid dates produces a number; text dates may cause an error or need conversion.

Calculate and average the row-by-row differences

With start dates in A2:A4 and end dates in B2:B4, enter this formula in C2:

=B2-A2

Fill it down through C4. The results are 3, 6, and 3. Then, with those differences in C2:C4, enter:

=AVERAGE(C2:C4)

The result is 4. This two-step approach is usually the easiest to audit because you can see each interval, spot a mismatched pair, and confirm how the average was formed. In Microsoft 365 for Windows, copy the formula down by dragging the fill handle or selecting the cells and pressing Ctrl+D. On a Mac, use Command+D to fill down. You can also copy and paste the formula.

Keep the start and end ranges aligned: A2 must pair with B2, A3 with B3, and so on. If a row’s end date belongs to a different event than its start date, Excel will still return a number, but the result will not represent the intended interval.

Use one formula for a compact result

If every row in the ranges contains a valid start and end date, Microsoft 365 for Windows can calculate the average directly. With start dates in A2:A10 and their corresponding end dates in B2:B10, use:

=AVERAGE(B2:B10-A2:A10)

The subtraction creates an array of row-by-row differences, and AVERAGE returns their mean. This compact method is convenient when the data is complete, but it hides the individual intervals. The helper-column method is generally more transparent when other people will review the workbook or when you need to investigate an unexpected average.

Do not use this compact formula blindly when some pairs are incomplete. A blank cell can behave like zero in arithmetic, creating a huge or otherwise misleading interval. Use a helper column that deliberately excludes incomplete rows instead, as described below.

Handle blank rows and unfinished date pairs

Many lists contain records that have started but are not finished, or empty rows included for future entries. A simple subtraction formula can produce misleading results for those records. A helper formula can show an interval only when both date cells are populated. With start dates in A2:A10 and end dates in B2:B10, enter this in C2 and fill it down:

=IF(AND(A2<>"",B2<>""),B2-A2,"")

With the calculated results in C2:C10, average them using:

=AVERAGE(C2:C10)

AVERAGE ignores text values, including the empty text returned by the formula, so incomplete rows are left out. It also ignores genuinely empty cells. If no row has a valid numeric difference, AVERAGE returns a division-by-zero error; that is a signal to check whether any complete date pairs exist rather than evidence of an average of zero.

Be deliberate about what counts as complete. If a blank end date means “still in progress,” excluding that row is usually sensible for completed-duration reporting. If you instead want the duration through today, that is a different calculation and should be stated clearly in the report. Avoid silently substituting today’s date, because the average will change as time passes.

Choose calendar days, business days, or inclusive counts

Ordinary date subtraction counts elapsed calendar days, including weekends and holidays. For processing times or service-level reporting, you may need working days instead. Excel’s NETWORKDAYS function counts weekdays between two dates and includes both endpoints when they are workdays. With a start date in A2, an end date in B2, and holiday dates in F2:F10, this formula returns the working-day count for that interval:

=NETWORKDAYS(A2,B2,F2:F10)

NETWORKDAYS is not simply the same measure as elapsed days: it uses an inclusive workday count. Decide whether your business definition includes the start date, the end date, or both, and keep that convention consistent before averaging the results. For a standard Monday-to-Friday schedule, NETWORKDAYS excludes Saturdays and Sundays; the optional holiday range excludes listed holidays as well. If your weekends differ, Excel also provides NETWORKDAYS.INTL for custom weekend patterns.

Another common distinction is inclusive calendar counting. If a report asks how many calendar dates are covered, including both the first and last date, a same-day interval counts as one date and the difference needs an adjustment. Do not add one automatically to elapsed-time calculations: a duration from the start of one date to the start of the next is one elapsed day, not two.

Understand times, formatting, and negative intervals

Excel dates can contain times as well as dates. The time portion is stored as a fraction of a day, so subtracting January 1 at noon from January 2 at noon returns exactly 1 day. A difference of 12 hours is 0.5, and 36 hours is 1.5. Therefore, the average may be a decimal even when every displayed date appears to have no time attached.

If you want to display a decimal number of days, format the result as Number rather than Date. Select the result cell and use Home > Number Format, then choose Number and set the desired decimal places. If the answer is meant to be a whole number, decide how to handle fractions: rounding, rounding up, or showing the decimal are different choices. For example, an average of 4.5 days should not be presented as 5 unless the reporting rule calls for rounding up.

A negative difference means the end date is earlier than the start date. Check for reversed dates, mismatched rows, or a legitimate interval that runs backward in time. Depending on the workbook’s date system and formatting, negative date results can display as hashes or an unexpected date-like value. Format the difference as Number to inspect the underlying signed value, then correct the inputs or explain why negative durations are valid for your analysis.

Average consecutive dates in a single list

Sometimes the data contains one date per event, and the question is the average gap between consecutive events rather than the duration of paired start and end dates. Put the dates in A2:A10, sorted from oldest to newest. With the earlier date in A2 and the next date in A3, enter this in B3 and fill down:

=A3-A2

Then, with the gaps in B3:B10, calculate:

=AVERAGE(B3:B10)

Sorting matters. If dates are out of order, a gap can be negative and the average may no longer describe the typical wait between events. Also decide how to handle duplicate dates: a duplicate produces a zero-day gap, which is correct if two events occurred on the same date, but may be a data-entry error if duplicates are not expected.

Common errors and ways to check the result

If Excel shows a date instead of a number, the result cell is probably formatted as a date. Change its format to Number or General. If the formula returns an error, check for text dates, extra spaces, or values that are not valid dates. You can also inspect a few rows manually: confirm that each end date is later than its start date and that the displayed differences match the calendar.

Other useful checks include:

  • Confirm that start and end ranges have the same number of rows and are aligned record by record.
  • Check whether blanks are being excluded intentionally rather than treated as zero dates.
  • Compare the average with the smallest and largest intervals. An average far outside their range usually indicates a formula or data problem.
  • Decide whether unusual but valid long intervals should remain in the average. A few outliers can pull the mean upward; the median may be a useful companion statistic when the typical middle interval matters.

The median is not a corrected version of the average; it answers a different question. The average gives every duration equal weight in the total, while the median identifies the middle value after sorting. Reporting both can help readers understand a dataset with a few exceptionally long waits.

Frequently Asked Questions

Can I average the difference between two date columns without a helper column?
Yes. With complete paired dates in A2:A10 and B2:B10 in Microsoft 365, use =AVERAGE(B2:B10-A2:A10). For incomplete data or a workbook that needs to be easy to audit, calculate each difference in a helper column and average that column.

Why does my average show as a date?
The result cell is likely formatted with a date number format. Select it, go to Home > Number Format, and choose Number or General. A duration such as 4 days is stored as the number 4, even if date formatting makes it look like a calendar date.

Should I add one to the date difference?
Only if you intend to count both endpoint dates inclusively. Subtracting January 1 from January 4 gives 3 elapsed days. Counting January 1, 2, 3, and 4 as covered dates gives 4 dates, which is a different measure.

How do I calculate the average working days between dates?
Calculate each interval with NETWORKDAYS, using the appropriate weekend and holiday rules, and then average those counts. Confirm whether your reporting definition includes the start and end workdays, because NETWORKDAYS counts workdays inclusively.

Why is the average a decimal?
The intervals may not divide evenly, or one or more dates may include times. A decimal is a valid average: 4.5 days means the mean duration is four and a half days. Round it only if your reporting convention requires rounding.

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.