Calculate Total DateDif Average Between 2 Columns (start, end) according to year expressed in days, hours, minutes

Question

I'm having trouble getting a formula to work to calculate the total average time between times (start and end). Sheet: https://docs.google.com/spreadsheets/d/1X8EdejdzXMQg6x8k7ig5Jcdvdh2xQegu9lq4i6088ME

DateDif is the function that will do this individually (but only for days), I know using the TEXT function with DateDif could work (in my head). I've had issues getting it to work with Average, segmenting by years:

=AVERAGE(DATEDIF(A2:A37,B2:B37,"d"))

I've also tried writing the following which hasn't worked, with me now confusing myself and head scratching as I've not managed to find a solution online:

=SUMIFS($B:$B,$A:$A,">=2020/01/01",$A:$A,"<2021/01/01")/COUNTIFS($A:$A,">=2020/01/01",$A:$A,"<2021/01/01")

The result I'm after is to have the total average duration for each year, reported in F12:H12.

I hope this makes sense and I've not confused you more than myself!

Thanks.

Answer 1

You can use the following formulas:

For 2020 use in cell D2

=IFERROR(AVERAGE(ArrayFormula(IF(YEAR($B$2:$B$37)=2020,($B$2:$B$37-$A$2:$A$37)))),0)

You will get a number like 4.475757576. Format the cell as Duration to get the hours (if not interested leave it as is).

Following that, you can use the following formula in cell D3

=INT(D2)&"D "& HOUR(MOD(D2,1))&"H "&MINUTE(MOD(D2,1))&"M "&SECOND(MOD(D2,1))&"S"

Changing the year you get corresponding results.

Please adjust ranges and cells to your needs.

Functions used:

• AVERAGE
• ArrayFormula
• IFERROR
• IF
• YEAR
• MOD
• INT
• HOUR
• MINUTE
• SECOND

Answer 2

SOLVED: =ArrayFormula(AVERAGE(FILTER(B2:B-A2:A,C2:C="A",B2:B>=DATEVALUE("2020-1-1"),B2:B<DATEVALUE("2020-4-1"))))