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.

Purevibe · Answer

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

marikamitsos · Answer

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

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

2 Answers

Add your own answers!

Ask a Question