sum of various period of gained experiences

[SanjayaGarg]

Dear all,

I have some data where a particular person gained experiences in various organizations. I want to get total experience he gained so far. For this, please refer to below table and help to sum up column 'C' :

​	A​	B​	C​	D​
1​	FROM​	TO​	EXPERIENCE​	Formula to get experience​
2​	24-07-1981	27-06-1982	0 Years, 11 Months, 3 Days	=IF(A2="","",DATEDIF(A2,B2,"Y")&" Years, "&DATEDIF(A2,B2,"YM")&" Months, "&DATEDIF(A2,B2,"MD")&" Days")
3​	18-11-1985	30-10-1988	2 Years, 11 Months, 12 Days
4​	04-12-1989	02-05-1993	3 Years, 4 Months, 29 Days
5​	03-05-1993	11-10-1998	5 Years, 5 Months, 8 Days
6​	12-10-1998	16-09-2001	2 Years, 11 Months, 4 Days
7​	17-09-2001	31-10-2002	1 Years, 1 Months, 14 Days

I want to get total of experience in column 'C' in this format only. Please help.

 

[JEC]

Try this one.
Close it with ctrl+shift+return(array formula)

=SUM(DATEDIF(A3:A8,B3:B8,"Y"))+INT(SUM(DATEDIF(A3:A8,B3:B8,"YM"))/12)&" Years, "&MOD(SUM(DATEDIF(A3:A8,B3:B8,"YM")),12)+INT(SUM(DATEDIF(A3:A8,B3:B8,"MD"))/30)&" Months, "&MOD(SUM(DATEDIF(A3:A8,B3:B8,"MD")),30)&" Days"

 

[SanjayaGarg]

Try this one.
Close it with ctrl+shift+return(array formula)

=SUM(DATEDIF(A3:A8,B3:B8,"Y"))+INT(SUM(DATEDIF(A3:A8,B3:B8,"YM"))/12)&" Years, "&MOD(SUM(DATEDIF(A3:A8,B3:B8,"YM")),12)+INT(SUM(DATEDIF(A3:A8,B3:B8,"MD"))/30)&" Months, "&MOD(SUM(DATEDIF(A3:A8,B3:B8,"MD")),30)&" Days"

 

[SanjayaGarg]

Thanks Jec, It is working fine. Thanks again

 

[JEC]

Thanks, you're welcome!