Average Days between 3 dates ? Google Sheets

user_16774801 0
Viewed 253
Column A  Column B  Column C
4/1/2020  4/21/2021 4/30/2021
4/1/2020  4/9/2021  4/27/2021
4/1/2020  4/15/2021 4/15/2021

Trying to find the average days between these dates, so instead of manually doing the subtraction and then finding the average. EX: 1st row, (21-1=20)(30-21=9) = 29, 29/2= 14.5 AVG of days.

I constructed a formula that makes my life easier for the zillion records I have :)

  • =AVERAGE(DATEDIF(A1,B1,"D"),DATEDIF(B1,C1,"D")) The problem with the formula, it gives an error for the 3rd row since the start_date(B3) is the same as the end_date(C3).

So I included an IFS statement but still get an error

  • =IFS(C1>B1,AVERAGE(DATEDIF(A1,B1,"D"),DATEDIF(B1,C1,"D")),FALSE,DATEDIF(A1,C1,"D"))

Any tips to work around this? Thanks in advance!

data-analysis google-sheets
3 Answers

If you have three dates d1, d2 and d3 then the average difference between pairs of dates is

((d2-d1)+(d3-d2))/2

which may be re-written as

(d2-d1+d3-d2)/2

But the d2's cancel out, so you are left with

(d3-d1)/2

Also, you don't have to use datedif, because dates are whole numbers and can just be subtracted from each other.

So you have a choice of:

=datedif(A1,C1,"D")/2

or

=(C1-A1)/2

or if you would like to use an array formula

=arrayformula(if(A:A="","",(C:C-A:A)/2))

Here are the results of using these three formulas:

enter image description here

user_38949170

I think the problem is the FALSE being used for the second condition: you're essentially saying, NEVER return the result associated with the FALSE!

The IFS() function returns the first answer for which the condition is true. For row 3, NONE of the conditions are true.

You might do better with a plain IF() function, unless you have other conditions you want to add. In the IF() function, the first parameter is the condition, the second is the expression to be used if the condition is true, and the third is the expression to be used if the condition is false (or, the "else" expression.)

=IF(C3>B3,AVERAGE(DATEDIF(A3,B3,"D"),DATEDIF(B3,C3,"D")),DATEDIF(A3,C3,"D"))
user_4903740 
=AVERAGE(DATEDIF(A1,B1+1,"D"),DATEDIF(B1,C1+1,"D"))
user_4315020
Related