Average of Years employed

  • Thread starter Thread starter SGee
  • Start date Start date
S

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
 
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)
 
How about doing the averages first:

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

Change the ranges to match.
 
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.
Click to expand...

One way is to base the calculation on the original data, rather than on the
concatenation; for example:
=INT((AVERAGE(E:E)-AVERAGE(D:D))/365.25) & " Yrs, " &
ROUND(12*MOD((AVERAGE(E:E)-AVERAGE(D: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.)
 
Try this array formula** :

D4:D20 = beginning employment date
E4:E20 = promotion date

=AVERAGE((E4:E20-D4:D20)/365.25)

You may want to round off the result** :

=ROUND(AVERAGE((E4:E20-D4:D20)/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.
 
Back
Top