Calculate average between multiple timeframes

[usernam123]

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

[#2]

@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:

The formula in C3 (and copied down) is:

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

B7 contains:

=AVERAGE(B3:B5)

[#3]

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.

[#4]

@usernam123 

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

[#5]

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

[#6]

@usernam123 

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

[#7]

@usernam123 

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

[#8]

@usernam123 

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"