# Sum total and Average of DateIf formula to calculate total time

#### JmeNola

##### New Member
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.

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### Dave Patton

##### Well-known Member
Where is the information on the jobs?

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

#### Dave Patton

##### Well-known Member
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

##### New Member
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

##### Well-known Member

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

Last edited:

#### Dave Patton

##### Well-known Member
"
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.

Replies
2
Views
179
Replies
7
Views
155
Replies
1
Views
386
Replies
14
Views
258
Replies
7
Views
375

1,130,096
Messages
5,640,087
Members
417,126
Latest member
Jeffman52

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back