2

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.

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.

enter image description here

Functions used:

| improve this answer | |
  • 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 at 21:14
0

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.