Google-sheets – How to calculate average duration in Google Sheets Result from years:months:day

google sheetsgoogle-sheets-arrayformulagoogle-sheets-dates

All I'm trying to do is to get an Average Duration to result in Google Sheets to the average duration from a set of duration years:months:days

enter image description here

I calculate DURATION using this formula:

=INT((K3-B3)/365)&" Years, "&INT(MOD((K3-B3),365)/30)&" Months, "&ROUND(MOD((MOD((K3-B3),365)),30),0)&" Days"

However, I cannot Average the total duration using Google Sheets standard average formula.

Best Answer

TOTAL:

=INT((SUM(ARRAYFORMULA(M1:M14-L1:L14)))/365)&" Years, "&
 INT(MOD((SUM(ARRAYFORMULA(M1:M14-L1:L14))),365)/30)&" Months, "&
 ROUND(MOD((MOD((SUM(ARRAYFORMULA(M1:M14-L1:L14))),365)),30),0)&" Days"

AVERAGE:

=INT((AVERAGE(ARRAYFORMULA(M1:M14-L1:L14)))/365)&" Years, "&
 INT(MOD((AVERAGE(ARRAYFORMULA(M1:M14-L1:L14))),365)/30)&" Months, "&
 ROUND(MOD((MOD((AVERAGE(ARRAYFORMULA(M1:M14-L1:L14))),365)),30),0)&" Days"

Related Topic