Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Sum Formula
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: http://www.excelforum.com/member.php...o&userid=21906 View this thread: http://www.excelforum.com/showthread...hreadid=520668 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Sum Formula
Try the following undocumented formula:- =DATEDIF(C1,D1,"Y") 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: http://www.excelforum.com/member.php...o&userid=17084 View this thread: http://www.excelforum.com/showthread...hreadid=520668 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Sum Formula
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: ID Employer Start Date End Date Years 0000001 Employer B 1998-10-01 2000-07-01 1.8 0000001 Employer C 1998-05-01 1998-08-01 0.3 0000002 Employer D 2000-02-01 2001-03-15 1.1 0000002 Employer E 1999-06-01 2000-01-01 0.6 0000003 Employer F 1998-06-01 1998-08-31 0.2 0000003 Employer G 1996-12-01 1998-05-31 1.5 0000004 Employer H 1998-03-30 1999-12-29 1.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: http://www.excelforum.com/member.php...o&userid=21906 View this thread: http://www.excelforum.com/showthread...hreadid=520668 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Sum Formula
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,"M")/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: http://www.excelforum.com/attachment.php?postid=4435 | +-------------------------------------------------------------------+ -- Gary Brown ------------------------------------------------------------------------ Gary Brown's Profile: http://www.excelforum.com/member.php...o&userid=17084 View this thread: http://www.excelforum.com/showthread...hreadid=520668 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Sum Formula? | Excel Discussion (Misc queries) | |||
need a conditional formula to generate numbers divisible by 4 between a given starting no. & end No. | Excel Worksheet Functions | |||
Conditional formatting...cont. from 9/25 | Excel Discussion (Misc queries) | |||
Conditional Formatting formula not acceptable? | Excel Discussion (Misc queries) | |||
Formula Dependant Conditional Formatting | Excel Discussion (Misc queries) |