Calculate Length of Experience

[aasher1]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Start Date
[/TD]
[TD]End Date
[/TD]
[TD]Experience
[/TD]
[/TR]
[TR]
[TD]Job 1
[/TD]
[TD]04/04/89
[/TD]
[TD]09/30/97
[/TD]
[TD]8 years, 5 months, 26 days
[/TD]
[/TR]
[TR]
[TD]Job 2
[/TD]
[TD]06/16/97
[/TD]
[TD]06/27/97
[/TD]
[TD]11 days
[/TD]
[/TR]
[TR]
[TD]Job 3
[/TD]
[TD]07/07/97
[/TD]
[TD]06/24/16
[/TD]
[TD]18 years, 11 months, 17 days
[/TD]
[/TR]
[TR]
[TD]Job 5
[/TD]
[TD]06/27/16
[/TD]
[TD]01/03/19
[/TD]
[TD]2 years, 6 months, 6 days
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Total
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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 ...

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Job​

[/td][td="bgcolor:#F3F3F3"]

Start Date​

[/td][td="bgcolor:#F3F3F3"]

End Date​

[/td][td="bgcolor:#F3F3F3"]

Experience [yrs]​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]Job 1[/td][td]

4/4/1989​

[/td][td]

9/30/1997​

[/td][td]

8.49​

[/td][td]D2: =(C2-B2)/365.25[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]Job 2[/td][td]

6/16/1997​

[/td][td]

6/27/1997​

[/td][td]

0.03​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]Job 3[/td][td]

7/7/1997​

[/td][td]

6/24/2016​

[/td][td]

18.97​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]Job 5[/td][td]

6/27/2016​

[/td][td]

1/3/2019​

[/td][td]

2.52​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td="bgcolor:#F3F3F3"]

Total​

[/td][td][/td][td][/td][td]

30.0​

[/td][td]D6: =SUM(D2:D5)[/td][/tr]
[/table]

 

[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.