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

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.

• 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. Jul 13, 2020 at 23:51

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.