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.

  • 1
    next time share a copy of your sheet!
    – user0
    Commented Feb 26, 2019 at 5:27

1 Answer 1



=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"


=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"


Your Answer

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

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