Adding dates.

shyam

Active Member
Joined
Jul 18, 2005
Messages
302
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
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

shyam

Active Member
Joined
Jul 18, 2005
Messages
302
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
Joined
Jul 21, 2002
Messages
73,092
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
Joined
Jul 18, 2005
Messages
302

ADVERTISEMENT

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
Joined
Jul 21, 2002
Messages
73,092
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
Joined
Jul 18, 2005
Messages
302

ADVERTISEMENT

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

Tks n rgds

Shyam
 

shyam

Active Member
Joined
Jul 18, 2005
Messages
302
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
Joined
Jul 21, 2002
Messages
73,092
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
Joined
Mar 23, 2005
Messages
20,825
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,564
Messages
5,596,875
Members
414,106
Latest member
Tigretto

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top