Summation of months or years

[Sandeep Nair]

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

 

[Ashish Mathur]

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

 

[joeu2004]

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)

 

[Sandeep Nair]

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

 

[Sandeep Nair]

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

 

[joeu2004]

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

 

[joeu2004]

Errata ....

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.