Sum total and Average of DateIf formula to calculate total time

[JmeNola]

Column A: Employee Name
Row 1: Column I: = TODAY()

Colum F:Start date: Actual date of hire
Column H: Total time in job: =DATEDIF(F4,$I$1,"Y")&" Years "&DATEDIF(F4,$I$1,"YM")&" Months "&DATEDIF(F4,$I$1,"MD")&" Days"

I need to sum the time for the employees from row 4 - 49 and take the average time in job.

then repeat for other job and create a graph for each job and highlight groups of employees with 0-6 months, 6 months - 1 year, 1 year - 2years..etc.

Can anyone help? I have the total time in the job calculated based on the formula above. not sure how to average the time among everyone or turn into a graph.

 

[Dave Patton]

Where is the information on the jobs?

Average =AVERAGE(F4:F49)+1-I1 Gives the average in days

 

[Dave Patton]

If the hire date is in F4:F49 and your require the average for that range use the following

=I1-(AVERAGE(F4:F49)+1)

This yields the average number of days employed for that that range of personnel.

Ignore the formula in my first post.

 

[JmeNola]

Where is the information on the jobs?

Average =AVERAGE(F4:F49)+1-I1 Gives the average in days

Thank you so much!

Is there a way to get the average back into Years, Months, and Days total. So if Employee A has 2 years and Employee B has 6 months, they would average to a total of 1.5 years on the job. Does that make sense?

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1	Name							Average time employed	23-Jan-18		656
2			1 Years 9 Months 16 Days
3
4	A					22-Jan-18					1
5	B					1-Jan-18					22
6	C					1-Jan-15					1118
7	D					1-Jan-14					1483
8
4a

Cell Formulas
Range	Formula
K1	=I1-(AVERAGE(F4:F51))
K4	=$I$1-F4
K5	=$I$1-F5
K6	=$I$1-F6
K7	=$I$1-F7
C2	=DATEDIF(1,K1,"Y")&" Years "&DATEDIF(1,K1,"YM")&" Months "&DATEDIF(1,K1,"MD")&" Days"

The formula in C2 yields Text. If you want to do calculations with the average, you may want to start with average days.

 

[Dave Patton]

"
Is there a way to get the average back into Years, Months, and Days total. So if Employee A has 2 years and Employee B has 6 months, they would average to a total of 1.5 years on the job. Does that make sense?"

You could also consider YearFrac.

Excel 2010
	C	D	E	F	G	H	I	J	K
1						Average time employed	23-Jan-18	Days	459.5
2	1 Years 3 Months 2 Days
3	1.257534		1.258904
4				18-Jan-16	2.016438				736
5				24-Jul-17	0.50137				183
4a (2)

Cell Formulas
Range	Formula
K1	=I1-(AVERAGE(F4:F49))
K4	=$I$1-F4
K5	=$I$1-F5
C2	=DATEDIF(1,K1,"Y")&" Years "&DATEDIF(1,K1,"YM")&" Months "&DATEDIF(1,K1,"MD")&" Days"
C3	=YEARFRAC(0,K1,3)
E3	=AVERAGE(G4:G5)
G4	=YEARFRAC(F4,$I$1,3)
G5	=YEARFRAC(F5,$I$1,3)