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.
Best Answer
You can use the following formulas:
For
2020
use in cellD2
You will get a number like
4.475757576
. Format the cell asDuration
to get the hours (if not interested leave it as is).Following that, you can use the following formula in cell
D3
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