How can i add months or years and get the answer in the same format. For
example, adding 2 years 10 months and 1 year 3 months. The answer should be 4
years 1 month. If we do this calculation in excel, as usual it will give the
answer as 3.13 (2.10+1.3).

Regards
Sandeep Nair

Hi,

I'd suggest that you convert all figures to months and then divide by 12.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Sandeep Nair" <Sandeep wrote in message
...
How can i add months or years and get the answer in the same format. For
example, adding 2 years 10 months and 1 year 3 months. The answer should
be 4
years 1 month. If we do this calculation in excel, as usual it will give
the
answer as 3.13 (2.10+1.3).

Regards
Sandeep Nair

I am intrigued as to what sort of Excel you have that gives 3.13 as the
result for 2.10+1.3. For the rest of us, 2.10+1.3 is the same as 2.1+1.3,
and it returns 3.4. If you wanted 3.13, you'd have to enter 2.10+1.03.
Similarly if you had 2 years and 1 month, that would have to be entered as
2.01 to distinguish it from your 2.10.

If you do want to enter data in that format, you might try
=INT(A1+A2)+INT((MOD(A1,1)*100+MOD(A2,1)*100)/12)+MOD((MOD(A1,1)*100+MOD(A2,1)*100),12)/100but you would be better off either entering everything in months, orentering years and months in separate columns.--David Biddulph"Sandeep Nair" <Sandeep wrote in ... How can i add months or years and get the answer in the same format. For example, adding 2 years 10 months and 1 year 3 months. The answer shouldbe 4 years 1 month. If we do this calculation in excel, as usual it will givethe answer as 3.13 (2.10+1.3). Regards Sandeep Nair

On Nov 3, 11:34*pm, Sandeep Nair <Sandeep
wrote:
How can i add months or years and get the answer
in the same format. For example, adding 2 years
10 months and 1 year 3 months. The answer should
be 4 years 1 month. If we do this calculation in
excel, as usual it will give the answer as 3.13
(2.10+1.3).

First, year/month values of this form should be entered as text, not
numbers; for example '2.10 and '1.3 (the first character is an
apostrophe, aka single quote). Otherwise, we cannot distinguish
between 2y 10m and 2y 1m, for example.

Assuming you do that, the following adds 2 such values in A1 and A2:

1. In a helper cell (A3), put:

=LEFT(A1,FIND(".",A1)-1)
+ RIGHT(A1,LEN(A1)-FIND(".",A1))/12
+ LEFT(A2,FIND(".",A2)-1)
+ RIGHT(A2,LEN(A2)-FIND(".",A2))/12

2. In another cell, compute the year/month result by:

=INT(A3) & "." & INT(MOD(A3,1)*12)

Of course, you could do it all in one cell, replacing each instance of
A3 with the formula in A3 (gulp!).

Alternatively (double gulp!):

=LEFT(A1,FIND(".",A1)-1) + LEFT(A2,FIND(".",A2)-1)
+ INT((RIGHT(A1,LEN(A1)-FIND(".",A1))
+ RIGHT(A2,LEN(A2)-FIND(".",A2)))/12)
& "." &
MOD((RIGHT(A1,LEN(A1)-FIND(".",A1))
+ RIGHT(A2,LEN(A2)-FIND(".",A2))), 12)

Sorry DAvid. It was a typing error. i meant 1.03 only

"David Biddulph" wrote:

I am intrigued as to what sort of Excel you have that gives 3.13 as the
result for 2.10+1.3. For the rest of us, 2.10+1.3 is the same as 2.1+1.3,
and it returns 3.4. If you wanted 3.13, you'd have to enter 2.10+1.03.
Similarly if you had 2 years and 1 month, that would have to be entered as
2.01 to distinguish it from your 2.10.

If you do want to enter data in that format, you might try
=INT(A1+A2)+INT((MOD(A1,1)*100+MOD(A2,1)*100)/12)+MOD((MOD(A1,1)*100+MOD(A2,1)*100),12)/100but you would be better off either entering everything in months, orentering years and months in separate columns.--David Biddulph"Sandeep Nair" <Sandeep wrote in ... How can i add months or years and get the answer in the same format. For example, adding 2 years 10 months and 1 year 3 months. The answer shouldbe 4 years 1 month. If we do this calculation in excel, as usual it will givethe answer as 3.13 (2.10+1.3). Regards Sandeep Nair

Also, i think it will b always good to give a solution for the question
instead of finding mistakes in the questions.

"David Biddulph" wrote:

I am intrigued as to what sort of Excel you have that gives 3.13 as the
result for 2.10+1.3. For the rest of us, 2.10+1.3 is the same as 2.1+1.3,
and it returns 3.4. If you wanted 3.13, you'd have to enter 2.10+1.03.
Similarly if you had 2 years and 1 month, that would have to be entered as
2.01 to distinguish it from your 2.10.

If you do want to enter data in that format, you might try
=INT(A1+A2)+INT((MOD(A1,1)*100+MOD(A2,1)*100)/12)+MOD((MOD(A1,1)*100+MOD(A2,1)*100),12)/100but you would be better off either entering everything in months, orentering years and months in separate columns.--David Biddulph"Sandeep Nair" <Sandeep wrote in ... How can i add months or years and get the answer in the same format. For example, adding 2 years 10 months and 1 year 3 months. The answer shouldbe 4 years 1 month. If we do this calculation in excel, as usual it will givethe answer as 3.13 (2.10+1.3). Regards Sandeep Nair

On Nov 4, 12:43*am, Sandeep Nair
wrote:
Also, i think it will b always good to give a
solution for the question instead of finding
mistakes in the questions.

I believe he did. I would only add the suggestion of one additional
INT to minimize rounding error, which might adversely affect
comparisons and propagating the result of the sum. To wit:

=INT(A1+A2)
+ INT((MOD(A1,1)*100 + MOD(A2,1)*100)/12)
+ MOD(INT(MOD(A1,1)*100 + MOD(A2*,1)*100), 12)/100

Errata ....

On Nov 4, 7:49*am, I wrote:
I would only add the suggestion of one additional
INT to minimize rounding error, which might adversely affect
comparisons and propagating the result of the sum.

Sorry. That should be ROUND, as follows:

= ROUND(INT(A1+A2)
* + INT((MOD(A1,1)*100 + MOD(A2,1)*100)/12)
+ MOD(MOD(A1,1)*100 + MOD(A2*,1)*100, 12)/100, 2)

To understand, try putting that formula in B1 and that formula without
ROUND into B2, then compute =(B1-B2) , with A1=2.03 and A2=1.03.

And with that change, the formula can be simplified somewhat:

= ROUND(INT(A1+A2)
+ INT(MOD(A1+A2,1)*100/12)
+ MOD(MOD(A1+A2,1)*100, 12)/100,2)

This assumes that the decimal fraction is always 0.01 to 0.11, and you
always use ROUND(...,2) around any computations.