Adding multiple date ranges

[Viswa88]

How to add multiple date ranges to find the total time period in number of years , months and days

4/12/2015 to 6/2/2019 = 4 years,1 months,21 days
9/8/2010 to 1/8/2012 = 1 years,4 months,0 days
5/3/2007 to 3/4/2008 = 0 years,10 months,1 days
3/5/2004 to 4/3/2006 = 1 years,10 months,1 days
----------------------------------------------------------------
total = ??????

new.xlsx
	A	B	C
1	from	to
2	4/12/2015	6/2/2019	4 years,1 months,21 days
3	9/8/2010	1/8/2012	1 years,4 months,0 days
4	5/3/2007	3/4/2008	0 years,10 months,1 days
5	03/05/2002	04/03/2006	3 years,10 months,1 days
6		total	?????
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=DATEDIF(A2,B2,"y") &" years,"&DATEDIF(A2,B2,"ym") &" months," &DATEDIF(A2,B2,"md") &" days"

 

[arthurbr]

In an unused cell, AA1 say, enter 01/01/1900
Then in AA2 enter =sum(B2:B5)-sum(A2:a5)
C6 is then

=DATEDIF(AA1,AA2,"y") &" years,"&DATEDIF(AA1,AA2,"ym") &" months," &DATEDIF(AA1,AA2,"md") &" days"

( you can of course enter the two values directly in the formula, but that makes it quite long to edit...

 

[Viswa88]

In an unused cell, AA1 say, enter 01/01/1900
Then in AA2 enter =sum(B2:B5)-sum(A2:a5)
C6 is then

=DATEDIF(AA1,AA2,"y") &" years,"&DATEDIF(AA1,AA2,"ym") &" months," &DATEDIF(AA1,AA2,"md") &" days"

( you can of course enter the two values directly in the formula, but that makes it quite long to edit...

This is not giving correct answer

new.xlsx
	A	B	C																								AA
1	from	to																									1/1/1900
2	4/12/2015	6/2/2019	4 years,1 months,21 days																								1401
3	9/8/2010	1/8/2012	1 years,4 months,0 days
4	5/3/2007	3/4/2008	0 years,10 months,1 days
5	03/05/2002	04/03/2006	3 years,10 months,1 days
6			3 years,10 months,0 days
Sheet1

Cell Formulas
Range	Formula
AA2	AA2	=SUM(B2:B5)-SUM(A2:A5)
C2:C5	C2	=DATEDIF(A2,B2,"y") &" years,"&DATEDIF(A2,B2,"ym") &" months," &DATEDIF(A2,B2,"md") &" days"
C6	C6	=DATEDIF(AA1,AA2,"y") &" years,"&DATEDIF(AA1,AA2,"ym") &" months," &DATEDIF(AA1,AA2,"md") &" days"

 

[Peter_SSs]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Adding multiple date ranges.
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[BSALV]

the number of days depends also on the day (in the month) of startdate

Map2
	A	B	C	D
1	from	to	tekst	delta
2	04-12-2015	06-02-2019	3 years, 2 months, 2 days	1160,00
3	09-08-2010	01-08-2012	1 years, 11 months, 23 days	723,00
4	05-03-2007	03-04-2008	1 years, 0 months, 29 days	395,00
5	03-05-2002	04-03-2006	3 years, 10 months, 1 days	1401,00
6				3679,00
7
8	20-01-2336	15-02-2346	10 years, 0 months, 26 days	3679,00
9
10	01-01-1900	27-01-1910	10 years, 0 months, 26 days
11	01-02-1900	27-02-1910	10 years, 0 months, 26 days
12	01-03-1900	28-03-1910	10 years, 0 months, 27 days
13	01-04-1900	28-04-1910	10 years, 0 months, 27 days
14	01-05-1900	28-05-1910	10 years, 0 months, 27 days
Blad3

Cell Formulas
Range	Formula
C2:C5,C10:C14,C8	C2	=DATEDIF(A2,B2,"y") &" years, " &DATEDIF(A2,B2,"ym") & " months, " &DATEDIF(A2,B2,"md") &" days"
D2:D5,D8	D2	=+B2-A2
D6	D6	=SUM(D2:D5)
A8:B8	A8	=SUM(A2:A5)
B10:B14	B10	=+A10+$D$8
A11:A14	A11	=EDATE(A10,1)

 

[BSALV]

a problem with the normal way of presenting dates and the US-dateformat, so the numberformat is YYYY-MM-DD

Map2
	A	B	C	D
1	from	to	tekst	delta
2	2015-04-12	2019-06-02	4 years, 1 months, 21 days	1512,00
3	2010-09-08	2012-01-08	1 years, 4 months, 0 days	487,00
4	2007-05-03	2008-03-04	0 years, 10 months, 1 days	306,00
5	2004-03-05	2006-04-03	2 years, 0 months, 29 days	759,00
6				3064,00
7
8	2337-06-28	2345-11-17	8 years, 4 months, 20 days	3064,00
9
10	1900-01-01	1908-05-22	8 years, 4 months, 21 days
11	1900-02-01	1908-06-22	8 years, 4 months, 21 days
12	1900-03-01	1908-07-21	8 years, 4 months, 20 days
13	1900-04-01	1908-08-21	8 years, 4 months, 20 days
14	1900-05-01	1908-09-20	8 years, 4 months, 19 days
Blad3

Cell Formulas
Range	Formula
C2:C5,C10:C14,C8	C2	=DATEDIF(A2,B2,"y") &" years, " &DATEDIF(A2,B2,"ym") & " months, " &DATEDIF(A2,B2,"md") &" days"
D2:D5,D8	D2	=+B2-A2
D6	D6	=SUM(D2:D5)
A8:B8	A8	=SUM(A2:A5)
B10:B14	B10	=+A10+$D$8
A11:A14	A11	=EDATE(A10,1)