New Contributor

# Calculate average between multiple timeframes

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

# Re: Calculate average between multiple timeframes

@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)``

# Re: Calculate average between multiple timeframes

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.

# Re: Calculate average between multiple timeframes

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

# Re: Calculate average between multiple timeframes

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

# Re: Calculate average between multiple timeframes

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

# Re: Calculate average between multiple timeframes

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

# Re: Calculate average between multiple timeframes

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