CODEWITHSUNDEEP
arraysif-statementgoogle-sheetsfilteraverage
Apollo-Roboto
Apollo-Roboto

Reputation: 780

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

Upvotes: 1

Views: 875

Answers (2)

player0
player0

Reputation: 1

use:

=AVERAGE(FILTER(DAYS(B2:B100, A2:A100), B2:B100<>""))

enter image description here

Upvotes: 1

Apollo-Roboto
Apollo-Roboto

Reputation: 780

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))))

Upvotes: 0

Related Questions