How to calculate the average number of days between two dates in Excel
Formula (Excel and Google Sheets)
=AVERAGE(FILTER(C2:C8-B2:B8,C2:C8<>""))Subtract the columns and average the closed rows: =AVERAGE(FILTER(C2:C8-B2:B8,C2:C8<>"")).
- Result on the example
- 4.3 in
F1 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in F1. Change any input and the result updates, or edit the formula to experiment.
How it works
C2:C8-B2:B8 subtracts each opened date from its closed date, giving the days to close for every ticket at once. FILTER keeps the closed tickets only, and AVERAGE averages them: 26 days over 6 tickets is 4.3.
=AVERAGE(FILTER(C2:C8-B2:B8,C2:C8<>""))Result4.333333333
C2:C87 rows × 1 col: 4/3/2026, 4/9/2026, 4/6/2026, 4/17/2026, …A range: 7 cells in one column.
B2:B87 rows × 1 col: 4/1/2026, 4/2/2026, 4/6/2026, 4/7/2026, …A range: 7 cells in one column.
C2:C8-B2:B8{7 rows × 1 col} 2, 7, 0, 10, …The - operator subtracts.
C2:C8<>""{7 rows × 1 col} TRUE, TRUE, TRUE, TRUE, …The <> operator tests whether they differ.
FILTER(C2:C8-B2:B8,C2:C8<>""){6 rows × 1 col} 2, 7, 0, 10, …FILTER: Rows of a range that meet every condition. #N/A when none match.- range
C2:C8-B2:B8{7 rows × 1 col} 2, 7, 0, 10, … - condition1
C2:C8<>""{7 rows × 1 col} TRUE, TRUE, TRUE, TRUE, …
- range
AVERAGE(FILTER(C2:C8-B2:B8,C2:C8<>""))4.333333333AVERAGE: Arithmetic mean of the numbers.- number1
FILTER(C2:C8-B2:B8,C2:C8<>""){6 rows × 1 col} 2, 7, 0, 10, …
- number1
In Google Sheets
The same formula works in Google Sheets: =AVERAGE(FILTER(C2:C8-B2:B8,C2:C8<>"")). We ran it in Google Sheets mode on the same data and it returns 4.3.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT((C2:C8<>"")*(C2:C8-B2:B8))/COUNT(C2:C8) | 4.3 | Works in every version: adds the days for closed tickets and divides by the number closed. |
| =AVERAGE(C2:C8-B2:B8) | -6585.7Different result | Includes the open ticket. The open ticket counts as a huge negative number. |
Common mistakes
- Open tickets with a blank close date count as 0 minus the open date, a huge negative number. Filter them out.
- For business days, add a helper column with
=NETWORKDAYS(B2,C2)-1and average that.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Calculate the average number of days between two dates
F1, calculate the average number of days from opened to closed, for the tickets that are closed.Questions
How do I get the median instead?
=MEDIAN(FILTER(C2:C8-B2:B8,C2:C8<>"")). It is less affected by a few slow tickets.
How do I average the gaps between dates in one column?
Subtract the column from itself shifted one row: =AVERAGE(B3:B8-B2:B7).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.