Calculate Length of Experience

[aasher1]

	Start Date	End Date	Experience
Job 1	04/04/89	09/30/97	8 years, 5 months, 26 days
Job 2	06/16/97	06/27/97	11 days
Job 3	07/07/97	06/24/16	18 years, 11 months, 17 days
Job 5	06/27/16	01/03/19	2 years, 6 months, 6 days
		Total

<tbody>
</tbody>

I would like to be able to calculate the total number of years, months, and days experience a person has from individual jobs. I have the DATEDIF function to calculate the time elapsed between dates for each job, but can't figure out how to get them to add together.

Thank you.

 

[Eric W]

You could figure out the total duration using

=(C2-B2)+(C3-B3)+(C4-B4)+(C5-B5)

Then use your DATEDIF formula using 0 as the start date, and that amount as the end date.

 

[shg]

That's a pretty strange way to show experience.

Q: Which is longer, 1 month and 1 day, or 30 days?

A: It depends.

Why not just ...

	A​	B​	C​	D​	E​
1​	Job​	Start Date​	End Date​	Experience [yrs]​
2​	Job 1	4/4/1989​	9/30/1997​	8.49​	D2: =(C2-B2)/365.25
3​	Job 2	6/16/1997​	6/27/1997​	0.03​
4​	Job 3	7/7/1997​	6/24/2016​	18.97​
5​	Job 5	6/27/2016​	1/3/2019​	2.52​
6​	Total​			30.0​	D6: =SUM(D2:D5)

 

[aasher1]

Thank you so much Eric W. It's always easier then what I try do.This is what I did.

=IF(DATEDIF(0,(C2-B2)+(C3-B3)+(C4-B4)+(C5-B5),"y")=0,"",DATEDIF(0,(C2-B2)+(C3-B3)+(C4-B4)+(C5-B5),"y")&"years,")&IF(DATEDIF(0,(C2-B2)+(C3-B3)+(C4-B4)+(C5-B5),"ym")=0,"",DATEDIF(0,(C2-B2)+(C3-B3)+(C4-B4)+(C5-B5),"ym")&"months, ")& IF(DATEDIF(0,(C2-B2)+(C3-B3)+(C4-B4)+(C5-B5),"md")=0,"",DATEDIF(0,(C2-B2)+(C3-B3)+(C4-B4)+(C5-B5),"md")&"days")


I thought it funny though, if using last date of 01/02/19 it gives me “29 years,11 months, 31 days” instead of just 30 years. Then using the last date of 01/03/19 it gives me “30 years, 1 days”

 

[Eric W]

First, here's a slightly shorter version of the formula:

=SUMPRODUCT(C2:C5-B2:B5)

Next, I've seen similar DATEDIF formulas to get the years/months/days. I'm not going to worry about why it generates "31 days" as part of it. After all, after 30 years, does it really matter if it's plus or minus a day or two? I think shg's approach has a lot of validity to it.

In any case, I'm glad we could help.