Calculate average between multiple timeframes

Copper Contributor

I have the following timeframes I calculated. I am trying to now calculate the average between all the dates and I cannot figure out the formula. Can anyone assist?


3 yr, 8 mo

5 yr, 1 mo

1 yr, 8 mo

8 yr, 1 mo

4 yr, 1 mo

8 yr, 9 mo

4 yr, 2 mo

0 yr, 9 mo

6 yr, 8 mo

9 yr, 5 mo

3 yr, 0 mo

0 yr, 0 mo

1 yr, 10 mo

4 yr, 2 mo

11 yr, 11 mo

7 Replies

@usernam123 How did you calculate these "timeframes"? If you could work with real numbers, it would be a lot easier. For instance, you could calculate 3.66667 in one cell and make it look like "3 yr, 8 mo" in another. Something like this:

Screenshot 2022-08-11 at 06.59.28.png

The formula in C3 (and copied down) is:

=INT(B3)&" yr, "&TEXT(MOD(B3,1)*12,"0")&" mo"

B7 contains:



I used the DATEDIF function. I understand it would be easier with real numbers which is why I am asking if anyone knows another way.


Do you have start dates and end dates? Or did you uses start dates and TODAY()?

the first start is a start date and the second date is TODAY()


As a comment, if dates are named range ages,


formula for average could be

=AVERAGE( LEFT(ages, FIND(" yr", ages) ) + SUBSTITUTE( RIGHT(ages, LEN(ages) - FIND(", ", ages) - 1 ), " mo", "")/12 )

transform to text with yr and mo as @Riny_van_Eekelen suggested


See the attached sample workbook for a way to calculate the average.


As variant for @Hans Vogelaar  sample

=INT( AVERAGE( TODAY() - A2:A25)/365.25 ) & " yr, " &  ROUNDDOWN( MOD( AVERAGE( TODAY() - A2:A25)/365.25, 1 )*12, 0 ) & " mo"