Average of Years employed

[SGee]

I am trying to find an average length of employment for a group of employees
at the time of their promotion. I am using the formula below to calculate
each employee's time in service at the date of promotion:

=DATEDIF(D4,E4,"y") & " Yrs, " & DATEDIF(D4,E4,"ym") & " Months"

where:
D4 = beginning employment date
E4 = promotion date

What I would like to do is average the time on the job for the employees
promoted in each group. I cannot simply average the column due (I think) to
the text in the output.

Any suggestions would be most appreciated. Thanks

Scott

 

[Fred Smith]

1. =e4-d4 gives you the number of days the person has been on staff.
2. Copy this down for all staff members
3. Average this range.
4. If you want to display the result the same way, you can use:
=DATEDIF(0,a1,"y") & " Yrs, " & DATEDIF(0,a1,"ym") & " Months"
where a1 holds the average.

Regards,
Fred

 

[Rajesh Mehmi]

Hi Scott

could you not split your formula below
=DATEDIF(D4,E4,"y") & " Yrs, " & DATEDIF(D4,E4,"ym") & " Months"
in the next two columns

for years Column G
=DATEDIF(D4,E4,"y")
for months Column H
=DATEDIF(D4,E4,"ym")

then average them
=SUM(G4:G17)+(QUOTIENT(SUM(H4:H17),12))& " Yrs " & MOD(SUM(H4:H17),12) &"
Months"


--
Best regards

Rajesh Mehmi

(e-mail address removed)

 

[Dave Peterson]

How about doing the averages first:

=datedif(average(d4:d999),average(e4:e999),"y") & " Years, " & .....

Change the ranges to match.

 

[MyVeryOwnSelf]

I am trying to find an average length of employment for a group of

employees at the time of their promotion. I am using the formula
below to calculate each employee's time in service at the date of
promotion:

=DATEDIF(D4,E4,"y") & " Yrs, " & DATEDIF(D4,E4,"ym") & " Months"

where:
D4 = beginning employment date
E4 = promotion date

What I would like to do is average the time on the job for the
employees promoted in each group. I cannot simply average the column
due (I think) to the text in the output.

One way is to base the calculation on the original data, rather than on the
concatenation; for example:
=INT((AVERAGE(E:E)-AVERAGE(D))/365.25) & " Yrs, " &
ROUND(12*MOD((AVERAGE(E:E)-AVERAGE(D))/365.25,1),0) & " Months"

Explanation: the average of a bunch of differences equals the difference of
the averages.

Modify to suit.

(I use Excel 2003.)

 

[T. Valko]

Try this array formula** :

D420 = beginning employment date
E4:E20 = promotion date

=AVERAGE((E4:E20-D420)/365.25)

You may want to round off the result** :

=ROUND(AVERAGE((E4:E20-D420)/365.25),1)

That'll round to 1 decimal place

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.