software

Hello -

I need help developing a formula for conditional sum - adding total
years of service for employees.

So, where my columns are A ID, B Employer, C Start Date, D End Date, E
Years at Job, I want to sum the years at job for each employee ID. Can
anyone help me with a formula to do that?

Thanks!--
johnnygirl51
------------------------------------------------------------------------
johnnygirl51's Profile: www.excelforum.com/member.php...oamp;userid=21906
View this thread: www.excelforum.com/showthread...hreadid=520668
Try the following undocumented formula:-

=DATEDIF(C1,D1,quot;Yquot;)

where C1 is the Start date cell, D1 is the End date cell and Y is the
date diffrence in years--
Gary Brown
------------------------------------------------------------------------
Gary Brown's Profile: www.excelforum.com/member.php...oamp;userid=17084
View this thread: www.excelforum.com/showthread...hreadid=520668
Hi Gary -

Thanks so much for responding. I'm not familiar with the DATEDIF
formula. I could be missing something or misreading something, but I'm
not sure that will get me where I need to be.

If my data looks like this:

IDEmployer Start Date End Date Years

0000001Employer B1998-10-012000-07-011.8
0000001Employer C1998-05-011998-08-010.3
0000002Employer D2000-02-012001-03-151.1
0000002Employer E1999-06-012000-01-010.6
0000003Employer F1998-06-011998-08-310.2
0000003Employer G1996-12-011998-05-311.5
0000004Employer H1998-03-301999-12-291.8

I want to be able to write a formula that will add 1.8 and 0.3 for
Employee 0000001 and then add 1.1 and 0.6 for Employee 0000002, so that
I can get a total number of years of previous experience for each
employee. I apologize if I'm missing something and appreciate your
patience and help on this!!!!!!--
johnnygirl51
------------------------------------------------------------------------
johnnygirl51's Profile: www.excelforum.com/member.php...oamp;userid=21906
View this thread: www.excelforum.com/showthread...hreadid=520668
The formula is the same, except you replace the Y with an M for months
divide this by 12, therfore the formula looks like the following:-

=DATEDIF(C1,D1,quot;Mquot;)/12

This will then give you as per your example:-

0000001 Employer B 1998-10-01 2000-07-01 1.75
0000001 Employer C 1998-05-01 1998-08-01 0.25
0000002 Employer D 2000-02-01 2001-03-15 1.08
0000002 Employer E 1999-06-01 2000-01-01 0.58
0000003 Employer F 1998-06-01 1998-08-31 0.17
0000003 Employer G 1996-12-01 1998-05-31 1.42
0000004 Employer H 1998-03-30 1999-12-29 1.67

Then you can either do a pivot table to get the totals for employee
0000001, 0000002 etc or you could have a table with the following
formulas

A B
1 0000001 =sumif(employee ids - table above,A1,datedif
calculation)
2 0000002 =sumif(employee ids - table above,A1,datedif
calculation)
3 0000003 =sumif(employee ids - table above,A1,datedif
calculation)
4 0000004 =sumif(employee ids - table above,A1,datedif
calculation)

I have enclosed a smaple zipped up spreadsheet for you to have a look
at -------------------------------------------------------------------
|Filename: Example1.zip |
|Download: www.excelforum.com/attachment.php?postid=4435 |
-------------------------------------------------------------------

--
Gary Brown
------------------------------------------------------------------------
Gary Brown's Profile: www.excelforum.com/member.php...oamp;userid=17084
View this thread: www.excelforum.com/showthread...hreadid=520668