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 |
CodePudding user response：
ArrayFormula turned out to be helpful for this one. After searching a bunch, this formula worked for me:
=ARRAYFORMULA(AVERAGE(IF(ISBLANK(B2:B100), "", DAYS(B2:B100, A2:A100))))
CodePudding user response：
use:
=AVERAGE(FILTER(DAYS(B2:B100, A2:A100), B2:B100<>""))
