arraysif-statementgoogle-sheetsfilteraverage
How to calculate the average difference of date between two column
I have two column of dates, start_date and end_date. I need to know the average days between the two. Some of the dates are blank and should be excluded from the result.
Sample Data (expecting an average of 2 days):
| start_date | end_date | |
|---|---|---|
| 2022-08-19 | 2022-08-21 | 2 |
| 2022-08-21 | ||
| 2022-08-15 | 2022-08-18 | 3 |
| 2022-08-18 | 2022-08-19 | 1 |
Solution
use:
=AVERAGE(FILTER(DAYS(B2:B100, A2:A100), B2:B100<>""))
