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:


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:


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!



2 Answers 2


You can use the following formulas:

For 2020 use in cell D2


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.

enter image description here

Functions used:

  • Thanks, that worked! However I needed to query a date range by Type (col C), and tried the following in row 14 (J-M): =IFERROR(AVERAGE(ArrayFormula(IFS($C2:$C,"A",YEAR($A2:$A)>=2020/1/1,($B2:$B-$A2:$A),YEAR($A2:$A)<2020/4/1,($B2:$B-$A2:$A)))),0) =IFERROR(AVERAGE(ArrayFormula(IFS($C2:$C,"B",YEAR($A2:$A)>=2020/1/1,($B2:$B-$A2:$A),YEAR($A2:$A)<2020/4/1,($B2:$B-$A2:$A)))),0) ... Can you take help here again if possible please?
    – Purevibe
    Jul 14, 2020 at 21:14

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.