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
  • Welcome. Please remember that as per site guidelines when an answer addresses your question, accept it and even upvote it so others can benefit as well. Commented Jul 13, 2020 at 23:51

2 Answers 2

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:

1
  • 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
    Commented Jul 14, 2020 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"))))

Your Answer

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