3

I'm trying to find the average time that an employee is employed with my company. Right now I have calculated the time for each terminated person individually and have it displayed as years, months, and Days. I'd like to add all of the time and divide it by the number of terminated employees and display it again as Year, month, day, to show the average amount of time that we employ people.

This is the formula I use for "Time with Company" column:

=DATEDIF(E2,F2,"y") & " years, " & DATEDIF(E2,F2,"ym") & " months, " & DATEDIF(E2,F2,"md") & " days "

Date of Hire        Date of Termination        Time with Company
5/11/2011           6/13/11                    0 years, 1 months, 2 days 
6/6/2011            8/28/11                    0 years, 2 months, 22 days 
8/15/2011           2/20/12                    0 years, 6 months, 5 days 
6/13/2011           5/13/12                    0 years, 11 months, 0 days
PeterH
  • 7,377
  • 20
  • 54
  • 82
Kandra Gervais
  • 31
  • 1
  • I think you are trying to find out the Average days the employee stayed in the company between tow given date, check the solution I've posted right now. – Rajesh Sinha Jul 25 '18 at 13:30

2 Answers2

5

You could use an array formula to get the AVERAGE time in days using the below:

=AVERAGE(F2:F100-E2:E100)

enter this using Ctrl + Shift + Enter

Change the F100 & E100 to suit your number of rows etc.

You do not need to calculate the time with company and sum it, then dived by number of terminations, AVERAGE will do this for you. This will then replace the dates used in your DATEDIF formula.

Full formula would look something like: 

=DATEDIF(0,AVERAGE(F2:F100-E2:E100),"y")&" years " &DATEDIF(0,AVERAGE(F2:F100-E2:E100),"ym")&" months "&DATEDIF(0,AVERAGE(F2:F100-E2:E100),"md")&" days"
PeterH
  • 7,377
  • 20
  • 54
  • 82
0

If you want to calculate Average days between two dates the employee stayed in the company then you can use either of the formulas shown below:

enter image description here

Option 1:

=INT(AVERAGE(DATE(YEAR(B2),MONTH(B2),DAY(B2))-DATE(YEAR(A2),MONTH(A2),DAY(A2))))&" Days"

Option 2:

=AVERAGE(IFERROR(DATEDIF(A2,B2,"d"),0))& " Days"
Rajesh Sinha
  • 8,995
  • 6
  • 15
  • 35
  • The "average" of a single value is that value. Wrapping a single date difference in AVERAGE doesn't accomplish anything, and isn't what the question asks. The question is about the average of the date differences of multiple people. "An employee" in the question means an "average employee", which means the average of example employees. Looks like somebody needs more coffee. :-) – fixer1234 Jul 25 '18 at 19:33
  • @fixer1234, thanks for valuable feed back,, but when I use other formulas shown here are also showing the period in terms of Year, Month & Days row wise, and if you convert it into days the figure matches my values like between two Dates. – Rajesh Sinha Jul 26 '18 at 04:15
  • Your per employee values match because you aren't doing anything that would change those components. But those components aren't the objective. The objective is the average of all of those components. Your answer never gets to the part that the OP wants to answer. You only replicated the raw data. – fixer1234 Jul 26 '18 at 05:39
  • I haven't tested Peter's formula, but it looks like he has a basic formula that finds the average difference, and then he uses it several times to build the text string, each time formatting the result to show a particular time component. – fixer1234 Jul 26 '18 at 08:06
  • @fixer1234, if I'm using part of Peter's formula , {=AVERAGE(IFERROR(DATEDIF(A276:A278,B276:B278,"md"),0))}& " as an Array formula then getting 18 Days instead of 78 (2months 18 Days), other side non array is giving `0 days` nd with mine for the full range =AVERAGE(IFERROR(DATEDIF(A276:A278,B276:B278,"d"),0))& " Days" gets `78 days`. And when I use the full version of Peter's formula evene as an Array getting #Value/#Num error. For me is quit confusing ,, help me to find the problematic part. Thanks – Rajesh Sinha Jul 26 '18 at 08:06
  • 1) DATEDIF doesn't appear to work as an array formula. 2) Even if it did, you need to average total interval, not the MOD()-type components. Peter's formula compares the average result to zero to allow using DATEDIF just to format the date components. – fixer1234 Jul 26 '18 at 16:15
  • re: DATEDIF in array formula -- it doesn't work in LO Calc. But if you're getting results, it may work in Excel. But your basic issue, like 18 vs. 78, is because you're averaging remainders, which won't work. You need to average the total intervals, then format the result. – fixer1234 Jul 26 '18 at 16:24