Adding dates.

[shyam]

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

 

[Andrew Poulsom]

I think you mean subtract rather than add. Look here:

http://www.cpearson.com/Excel/datedif.aspx

 

[shyam]

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]

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]

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]

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]

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

Tks n rgds

Shyam

 

[shyam]

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]

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]

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