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 Feb 26 '19 at 5:27


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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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