#### shyam

##### Active Member
How does one add the result of two dates : eg.

15-07
00-03

Answer should be 15 years and 10 months

OR

15-07
00-06

Answer should be 16 years and 1 month

Thanks and regards

Shyam

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### shyam

##### Active Member
What I had mentioned is the result of the two dates using the combination of text and datedif commands. eg. given below:

Date of Joining Date of Leaving Diff.
03/01/1984 31/08/1999 15-07
01/10/2007 21/01/2008 00-03

Formula used in C2is =TEXT(DATEDIF(A2,B2,"y"),"00")&"-"&TEXT(DATEDIF(A2,B2,"ym"),"00")

Now I need to add the result of the two dates, i.e. 15-07 (15 years and 7 months) and 00 years and 03 months, which should be 15 years and 10 months. It should be in Years-month format (YY-MM). In case the month exceeds 12, the year should be 16 years and 1 month. eg :

15-07
00-06

Answer should be 16 years and 1 month

Tks n rgds

Shyam

#### Andrew Poulsom

##### MrExcel MVP
Sorry I misunderstood. Try:

=DATEDIF("30/11/1999"+0,TEXT("20"&C2&"-01","yyyy-mm-dd")+TEXT("20"&C3&"-01","yyyy-mm-dd")-"30/11/1999","y")&" years "&DATEDIF("30/11/1999"+0,TEXT("20"&C2&"-01","yyyy-mm-dd")+TEXT("20"&C3&"-01","yyyy-mm-dd")-"30/11/1999","ym")&" months"

#### shyam

##### Active Member

Dear Mr. Andrew,

Your answer is coming out correctly..but I am unable to understand the following:

1. what does 30/11/1999 and 20 refers to in your formula ?
2. Whether the cell reference can be given directly instead of the date in quotes?
3. In case I need the result in YY-MM format, the answer should come as 15-10
(15 years and 10 months).
4. If the months goes above 12 then does it automatically taken care of incrementing the years, i.e. 16-01 months ?

Would appreciate if you can explain the above..

Tks n rgds
Shyam

#### Andrew Poulsom

##### MrExcel MVP
This formula:

=TEXT("20"&C2&"-01","yyyy-mm-dd")+0

will convert the entry in C2 to a serial date of 1st of the month, eg 15-07 will become 1 July 2015. The 20 converts the year from 2 digits (15) to 4 digits (2015). The date 30 November 1999 is deducted to return 15 years and 7 months. That can be a reference to a cell containing that date.

To preserve your original format (yy-mm) use:

=TEXT(DATEDIF("30/11/1999"+0,TEXT("20"&C2&"-01","yyyy-mm-dd")+TEXT("20"&C3&"-01","yyyy-mm-dd")-"30/11/1999","y"),"00")&"-"&TEXT(DATEDIF("30/11/1999"+0,TEXT("20"&C2&"-01","yyyy-mm-dd")+TEXT("20"&C3&"-01","yyyy-mm-dd")-"30/11/1999","ym"),"00")

That formula (and my original) works for:

15-07
00-06

returning 16-01.

#### shyam

##### Active Member

Thank you very much sir for your explanation and formula...

Tks n rgds

Shyam

#### shyam

##### Active Member
one new query further to your reply.... suppose if I have one more set of dates to add

Date of Joining Date of Leaving Diff.
03/01/1984 31/08/1999 15-07
01/10/2007 21/01/2008 00-03
01/04/2008 21/07/2008 00-03

then where do I fix the syntax of the formula ? it is a little difficult for me to judge where one set of formula is starting and ending... your formula goes like this which is working for 2 set of dates...Also kindly mention where one set is starting and ending...

--------------------
To preserve your original format (yy-mm) use:

=TEXT(DATEDIF("30/11/1999"+0,TEXT("20"&C2&"-01","yyyy-mm-dd")+TEXT("20"&C3&"-01","yyyy-mm-dd")-"30/11/1999","y"),"00")&"-"&TEXT(DATEDIF("30/11/1999"+0,TEXT("20"&C2&"-01","yyyy-mm-dd")+TEXT("20"&C3&"-01","yyyy-mm-dd")-"30/11/1999","ym"),"00")

That formula (and my original) works for:

15-07
00-06

returning 16-01.
------------------------

Would appreciate if you could revert on the above...

Tks n rgds

Shyam

#### Andrew Poulsom

##### MrExcel MVP
My formula can only add 2 dates. In the example above those dates are in 2 cells, C2 and C3. The formula is split at the &"-"&, the first half being the years and the second the months. If that formula is in D3, you can add what's in C4 with:

=TEXT(DATEDIF("30/11/1999"+0,TEXT("20"&D3&"-01","yyyy-mm-dd")+TEXT("20"&C4&"-01","yyyy-mm-dd")-"30/11/1999","y"),"00")&"-"&TEXT(DATEDIF("30/11/1999"+0,TEXT("20"&D3&"-01","yyyy-mm-dd")+TEXT("20"&C4&"-01","yyyy-mm-dd")-"30/11/1999","ym"),"00")

which returns 16-01.

#### barry houdini

##### MrExcel MVP
If you want the result to add the results as given, i.e. for a result of 16-01 try

=TEXT((SUMPRODUCT(LEFT(C2:C4,2)+0)+INT(SUMPRODUCT(RIGHT(C2:C4,2)+0)/12))*100+MOD(SUMPRODUCT(RIGHT(C2:C4,2)+0),12),"00-00")

You can extend the range to cope with any number of sets

You could use this formula based on the original dates:

=TEXT(DATEDIF(0,SUM(B2:B4)-SUM(A2:A4),"y")*100+DATEDIF(0,SUM(B2:B4)-SUM(A2:A4),"ym"),"00-00")

but this won't necessarily give you the same result. This is because your column C formula is effectively rounding everything down, a difference of 110 days becomes just 00-03 so two differences of 110 days sums to 00-06. The second formula gives 00-07 because the total, 220 days, is in fact 7 months

Replies
4
Views
225
Replies
3
Views
212
Replies
15
Views
313
Replies
0
Views
189
Replies
13
Views
490

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,867
Messages
5,834,096
Members
430,260
Latest member
MANICX100

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back