# How to calculate the average number of days between two dates in Excel

Subtract the columns and average the closed rows: `=AVERAGE(FILTER(C2:C8-B2:B8,C2:C8<>""))`.

**Excel:** `=AVERAGE(FILTER(C2:C8-B2:B8,C2:C8<>""))` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Ticket | Opened | Closed |  | Average days to close |
| HD-501 | 2026-04-01 | 2026-04-03 |  |  |
| HD-502 | 2026-04-02 | 2026-04-09 |  |  |
| HD-503 | 2026-04-06 | 2026-04-06 |  |  |
| HD-504 | 2026-04-07 | 2026-04-17 |  |  |
| HD-505 | 2026-04-13 | 2026-04-16 |  |  |
| HD-506 | 2026-04-14 |  |  |  |
| HD-507 | 2026-04-20 | 2026-04-24 |  |  |

Formula in F1: `=AVERAGE(FILTER(C2:C8-B2:B8,C2:C8<>""))`

Result: 4.3

## 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.

## Other ways

- `=SUMPRODUCT((C2:C8<>"")*(C2:C8-B2:B8))/COUNT(C2:C8)`: Works in every version: adds the days for closed tickets and divides by the number closed.
- `=AVERAGE(C2:C8-B2:B8)`: 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)-1` and average that.

## 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)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/average-days-between-two-dates