Karen has attended:
Excel Intermediate course
Calculate Average Length of Service
Hi there,
Im trying to calculate the average employee’s length of service.
I tried using the formula =DATEDIF to get the length of service which I have.
However, when I click the average option, ironically I get the error message ‘#DIV/0!’
I do feel like a div, so any help would be greatly appreciated.
Thanks in advance
Karen
RE: Calculate Average Length of Service
Hi Karen,
You should first use a datedif function to calculate how many years each emplyee is in service i.e.
Datedif(Start date,Today(),"y") will calculate the years in service where start date should be the cell containing the start date of the employee.
Once you have that calculated column you can average the column containing the datedif functions
Hope this helps
RE: Calculate Average Length of Service
Hi Karen,
Thank you for the forum question.
If you have an employees list and you have a column with the hire date and a cell with =Today() and you want the average of all employees’s length of service. It is correct you can use the Datedif function =Datedif("the hiredate", "the current date (make a cell reference to the cell in which you have the today function)", "y" if you want it in years "M" if you want it in months "d" if you want it in days). Let us just imaging that have calculated the length of service for each employee in the range A2:A20. Select where you want to see the average and type =Average(A2:A20). This will now return the average length of service for all employees.
I hope this makes sense.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
