# Average of datedif data

Copper Contributor

# Average of datedif data

Hi, I'm trying (and failing) to average a column of datedif data (expressed as years and months). The formula used to create the datedif entries is =DATEDIF(O46, P46, "y") &" yrs, "&DATEDIF(O46, P46, "ym") &" mths". I need the average for rows 2 through 46, but I can't seem to write anything that works. Many thanks!!

5 Replies

# Re: Average of datedif data

The formula

=DATEDIF(O46, P46, "y") &" yrs, "&DATEDIF(O46, P46, "ym") &" mths"

returns a text value; you cannot calculate the average of text values.

Use the following formula instead in row 2 (in a different column if you prefer):

=DATEDIF(O2, P2, "m")

Fill down to row 46.

Let's say the new formula is in W2:W46.

The average is

=LET(a, AVERAGE(W2:W46), QUOTIENT(a, 12)&" yrs, "&MOD(a, 12)&" mths")

# Re: Average of datedif data

@HansVogelaar Thanks so much! That works brilliantly. One further question: is there a way to round up or down the months? Right now the average is coming out at e.g. 3 yrs, 4.276545 mths. Thank you so much!

# Re: Average of datedif data

Yes:

=LET(a, AVERAGE(W2:W46), QUOTIENT(a, 12)&" yrs, "&ROUND(MOD(a, 12), 0)&" mths")

# Re: Average of datedif data

Although you can't average the differences because they are text and not numbers, you can difference the averages.

``````= LET(
averageStart, AVERAGE(startDate),
averageEnd,   AVERAGE(endDate),
avDifference, DATEDIF(averageStart,averageEnd,"y") & "yr " &
DATEDIF(averageStart, averageEnd,"ym") & "mths",
avDifference
)``````

Thank you!