Yesterday, one of my friend asked me a question. It was quite simple, he had a data stored in specific way, and he wanted to do additions of few columns….
Question:
He had an Excel file, where data is stored in Years.Month
format (i.e. 5 years 8 months is stored as 5.08) in two columns. He wanted to
add these columns. Returned value should be in Years.Month format only. For
e.g. 5.08 + 8.05 (i.e 5 Years and 8 Months + 8 years and 5 Months) the answer
should be 14.01 (i.e. 14 Years and 1 Months). A simple addition formula in
Excel will return 13.13 as Excel considers these values as normal numeric
values. Hence he wanted some way to do these
additions.
I gave him the solution by writing below mentioned formula. Above mentioned situation may be common for many of us hence thought
of sharing it here for others.
Solution:
We can achieve desired output by using below mentioned
formula. Assuming that 2 data items are
stored in cell A2 and Cell B2, formula will be…
=IF(IF(MOD(A2,1)=0,0,VALUE(MID(A2&"0",FIND(".",A2)+1,2)))
+
IF(MOD(B2,1)=0,0,VALUE(MID(B2&"0",FIND(".",B2)+1,2)))>=12,
VALUE(1+INT(A2)+INT(B2) & "." &
RIGHT(0&IF(MOD(A2,1)=0,0,VALUE(MID(A2&"0",FIND(".",A2)+1,2)))
+ IF(MOD(B2,1)=0,0,VALUE(MID(B2&"0",FIND(".",B2)+1,2)))
- 12,2)),
VALUE(INT(A2)+INT(B2) & "." &
RIGHT("0"&IF(MOD(A2,1)=0,0,VALUE(MID(A2&"0",FIND(".",A2)+1,2)))
+
IF(MOD(B2,1)=0,0,VALUE(MID(B2&"0",FIND(".",B2)+1,2))),2)))
Explanation:
Logic: We
are adding Numeric portion (i.e. the years) and the Decimals portion (i.e. the
months) separately. If summation of decimal portion is greater than 12 then 1
is added into numeric summation (i.e. 1 is added into the summation of years as
12 months is equivalent to 1 year) and at the same time decimal summation (i.e.
summation of month) is reduced by 12.
Finally after addition, both numeric and decimal portion are
concatenated with a dot (decimal sign) and shown as numerical value using the
VALUE function.
IF(IF(MOD(A2,1)=0,0,VALUE(MID(A2&"0",FIND(".",A2)+1,2))):
It returns decimal portion from the given value. E.g. for 5.05 it will return
5, for 6.10 it will return 10 and so on. If there is no decimal value then it
returns 0. MOD is used to check if the given value has any decimal portion or
not. If there is any decimal portion, MID function is used to take out only the
decimal portion.
INT(A2): returns
the Numeric portion of the given value. For e.g. for 5.05 it will return 5, for
6.10 it will return 6 and so on.
VALUE: function
is used to convert text into values. All the operations are done by text
functions and the final outcome is also a text. To convert this text into
numeric value, Value function is used.
Hi Ritesh,
ReplyDeleteIt's really very great and helpful post that you created here. I was looking for this since long but could not crack it. It saved my lot of manuall efforts which i used to put-in to achieve this. Thnx a ton BOSS!!!
It would be nice if you just address below issues as well in this -
1) It works well when you have two dates basically, but, goes for toss when using for a single date value only.
e.g. If I have to caluculate the Years and Months (in the same format which you have considered) with only one date, say, 23-Dec-04, it does not work well.
2)Also it wold be helpful if you could sahre the formula to fetch out the lencgth and value of decimal part only.
Again many many thnx for such a nice blog. :)
Hello,
DeleteThanks for the feedback.
To answer your questions:
1. This formula is designed assuming that data is stored in specific way i.e. is in years.months format only and it may not give desired result if it is applied on other formats like date. The purpose is to give fair idea about how we can use various inbuilt functions like MOD, FIND, MID etc. to perform such operations. If you wish to use it in some other conditions, you will have to tweak this formula or design new formula based on the requirement using similar logic. Can you please elaborate it further, like how is the data stored, expected outcome etc.; I can help you with the formula.
2.Assuming your value is in A2 cell…
a) Formula to get the length of decimal part only: =LEN(A2)-FIND(".",A2)
b) Formula to get value of decimal part only: =VALUE(RIGHT(A2,LEN(A2)-FIND(".",A2)))
Hope this help…
Thanks!