Forum Discussion

Copper Contributor

Sep 28, 2023

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,...

excel

Formulas and Functions

willf100

Copper Contributor

Sep 28, 2023

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!

PeterBartholomew1

Silver Contributor

Sep 28, 2023

willf100

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
  )