Calculate Years/Months Between Dates and then Average

[Guest]

We are using the formula =DATEDIF(A1,NOW(),"y") & "." &
DATEDIF(A1,NOW(),"ym") to calculate the years and months from an employee
hire date.

Is there an easy way to average the years and months for a group of
employees with the results of that formula?

 

[Jason Morin]

You could use:

=AVERAGE(A1:A10*1)

Array-entered, meaning press ctrl-shift-enter.

But you could also just change your current formula to:

=1*(DATEDIF(A1,NOW(),"y") & "." & DATEDIF(A1,NOW(),"ym"))

Then just use:

=AVERAGE(A1:A10)

which is *not* array-entered.

HTH
Jason
Atlanta, GA

 

[Myrna Larson]

=1*(DATEDIF(A1,NOW(),"y") & "." & DATEDIF(A1,NOW(),"ym"))

I don't think that will work correctly. Here's the first problem: the formula
gives the same fractional part for both 1 month and 10 months, i.e. 5y1m =
5.1, 5y10m=5.1

But correcting the formula to give 5.01 and 5.10 will not work either.

Let's say you have these time intervals: 11y11m, 13y11m, 5y11m, 4y9m. If you
generate the decimal numbers 11.11, 13.11, 5.11, and 4.09, the sum is 33.42,
and 33.42/4 = 8.355. What does that mean? If the terminology is consistent, it
should mean 8 years and 35.5 months, or 10 years and 11.5 months.

But if you add the years and months separately, you get 33 years and 42
months, which is equivalent to 33 years + 3 years + 6 months = 36.5 years, and
the average is 9.125 years, or 9 years and 1.5 months.

Some suggestions for solutions:

1) generate the elapsed time in decimal years

=DATEDIF(A1,NOW(),"y")+DATEDIF(A1,NOW(),"ym")/12

2) generate the intervals in months, i.e. =DATEDIF(A1,NOW(),"m"), average the
months, then convert the average to years and months with =INT(X/12) and
MOD(X,12)

3) generate the interval as

=DATEDIF(A1,NOW(),"y")+DATEDIF(A1,NOW(),"ym")/100

(note the above gives 5.01 for 5y1m and 5.10 for 5y10m)

Then use the DOLLARDE function (from the ATP) to translate the numbers to
decimal years so they can be averaged correctly, and the DOLLARFR function to
convert the result back to the yy.mm format.

 

[Ron Rosenfeld]

We are using the formula =DATEDIF(A1,NOW(),"y") & "." &
DATEDIF(A1,NOW(),"ym") to calculate the years and months from an employee
hire date.

Is there an easy way to average the years and months for a group of
employees with the results of that formula?

Since Months and Years have differing number of days, your answer will be
approximate in any event.

You could just average the number of days for each employee, and then convert
that to some approximation of years and months.

For example, if you have a bunch of hire dates in A1:A10, and the average year
has 365.25 days, and the average month has 365.25/12 days, then the array
formula:

=INT(AVERAGE(TODAY()-A1:A10)/365.25)+MOD(
AVERAGE(TODAY()-A1:A10),365.25)/365.25*12/100

should give you an answer in a similar format.

To enter an array formula, hold down <ctrl><shift> while hitting <enter>. XL
should place braces {...} around the formula.


--ron