when i saw this post before 2 days or so i thought why this person asking such simple question then just now i came to know its always challenge to work with dates & time in excel, in this case you have to make excel understand that 5.2 is 5 years and 2 months , once you can do this then easily you can add.
excel always takes fractions on basis of full integer, / whole 100% ..... and not as you wish, so when in excel you enter, 5.2 years means 5 years and 0.2 year meaning 2.4 months and not 2 months.
that is why 5.2 x 12 gives 62.4 months and not 62 months as you want it to be.
Please understand the formulae, fractions and dates in excel as to how excel interprets the decimal. You have to write a code or use formula which would make excel understand that 5.2 means 5 is years and 2 is months so when we add 5.2 to 1.11 excel should understand to seperate integer & decimal add integers 5+1 = 6 and add decimal 2+11 = 13 and then again convert the decimal result 13 to yy.mm i.e 1.1 and finally add 1.1 to 6 making it 7.1
I have tried to do the same but not able to bring all in a single formula, see the excel sheet it takes two columns and final result is in third column so once you enter all formulas you can hide the two columns, check the formula and edit it if you feel you can combine into one formula.
and your question itself is incorrect, not clear, being IT student you could have clearly specified that you want excel to interpret a number as yy.mm, with integer as year and decimal as months and add such cells. The direct quoting of example confused members.
From India, Madras
113excel always takes fractions on basis of full integer, / whole 100% ..... and not as you wish, so when in excel you enter, 5.2 years means 5 years and 0.2 year meaning 2.4 months and not 2 months.
that is why 5.2 x 12 gives 62.4 months and not 62 months as you want it to be.
Please understand the formulae, fractions and dates in excel as to how excel interprets the decimal. You have to write a code or use formula which would make excel understand that 5.2 means 5 is years and 2 is months so when we add 5.2 to 1.11 excel should understand to seperate integer & decimal add integers 5+1 = 6 and add decimal 2+11 = 13 and then again convert the decimal result 13 to yy.mm i.e 1.1 and finally add 1.1 to 6 making it 7.1
I have tried to do the same but not able to bring all in a single formula, see the excel sheet it takes two columns and final result is in third column so once you enter all formulas you can hide the two columns, check the formula and edit it if you feel you can combine into one formula.
and your question itself is incorrect, not clear, being IT student you could have clearly specified that you want excel to interpret a number as yy.mm, with integer as year and decimal as months and add such cells. The direct quoting of example confused members.
From India, Madras
update enter this formula in G21 =INT(C21+B21)+INT(E21/12)&"."& MOD(E21,12) and make total formula columns to 2 cells,hope taking this into guidance you can move further and achieve your answer.
From India, Madras
From India, Madras
Dear Dattatraya,
Noted sarcasm in your reply to
.Hopefully you are looking for a solution and not a forum for time pass.
The problem of "adding two data" expressed in "year and months" is very very simple.It is being taught in class 3 or class 4. Obviously it is not a difficult query.It is given in elementary mathematics books , addition and subtraction is always of similar items and obviously code writers of excel are not ignorant of this and hence standard fromulas are there to solve problem of this kind .
The difficulty was in framing the " right statement " for query . And there are more than one way of extracting information from "wrong format " and putting it in correct format , adding them and reconverting back to "wrong format". That is why I asked whether you are willing to forego "wrong format " of writing year and month in one cell ? . But no reponse.
Again I am repeating , problem of "adding two data" expressed in "year and months" is very very simple . You can make it simpler by formatting corectly.
From India, Mumbai
Noted sarcasm in your reply to
The problem of "adding two data" expressed in "year and months" is very very simple.It is being taught in class 3 or class 4. Obviously it is not a difficult query.It is given in elementary mathematics books , addition and subtraction is always of similar items and obviously code writers of excel are not ignorant of this and hence standard fromulas are there to solve problem of this kind .
The difficulty was in framing the " right statement " for query . And there are more than one way of extracting information from "wrong format " and putting it in correct format , adding them and reconverting back to "wrong format". That is why I asked whether you are willing to forego "wrong format " of writing year and month in one cell ? . But no reponse.
Again I am repeating , problem of "adding two data" expressed in "year and months" is very very simple . You can make it simpler by formatting corectly.
From India, Mumbai
the simplest and easiest suggestion submitted seems to be to convert all to month - multiply years into 12, add, and then divide total by 12. least chance of error here.
From India, Mumbai
From India, Mumbai
Dear Premendra Pranay.
Sorry for not replying sir,
but generally i use INTERNET in the morning only because i am IT student as well as i am working as HR coordinator in one of the reputed company.so i do not get much time to use net.and today morning when i checked my mail then ravi's post was in front of my eyes so checked this and i reply to ravi for appreciating and to say thanks.
and before writing this post i hv seen your solution (i.e = int(a1)+int(a2)+quotient((right(a1,2)+right(a2,2)) ,12) +( mod((right(a1,2)+right(a2,2)),12))/100)
the solution is so good;and yes this was not so difficult but its not so easy so that any one can solve it in minute.
From India, Mumbai
Sorry for not replying sir,
but generally i use INTERNET in the morning only because i am IT student as well as i am working as HR coordinator in one of the reputed company.so i do not get much time to use net.and today morning when i checked my mail then ravi's post was in front of my eyes so checked this and i reply to ravi for appreciating and to say thanks.
and before writing this post i hv seen your solution (i.e = int(a1)+int(a2)+quotient((right(a1,2)+right(a2,2)) ,12) +( mod((right(a1,2)+right(a2,2)),12))/100)
the solution is so good;and yes this was not so difficult but its not so easy so that any one can solve it in minute.
From India, Mumbai
Find the below formula;
Put, 5.02 in A1 & 7.11 in B1 then copy below formula in C1
=ROUNDDOWN(A1,0)+ROUNDDOWN(B1,0)+IF(A1-ROUNDDOWN(A1,0)+B1-ROUNDDOWN(B1,0)>0.11,1+(A1-ROUNDDOWN(A1,0)+B1-ROUNDDOWN(B1,0)-0.12),A1-ROUNDDOWN(A1,0)+B1-ROUNDDOWN(B1,0))
or find the attached XL sheet.
From India, Mumbai
Put, 5.02 in A1 & 7.11 in B1 then copy below formula in C1
=ROUNDDOWN(A1,0)+ROUNDDOWN(B1,0)+IF(A1-ROUNDDOWN(A1,0)+B1-ROUNDDOWN(B1,0)>0.11,1+(A1-ROUNDDOWN(A1,0)+B1-ROUNDDOWN(B1,0)-0.12),A1-ROUNDDOWN(A1,0)+B1-ROUNDDOWN(B1,0))
or find the attached XL sheet.
From India, Mumbai
In fact the question has wrongly been formulated. You cannot express 5 years and 2 months as 5.2 years. 0.2 is 2/10 and 2 months is 2/12 that is 0.16666. If you wanted to express 5 yrs and 2 months in decimals then it should be expressed as 5.1666666. Similarly 1 yr and 11 month would be 1.92 in decimals which would give a sum of approximately 7.08 yrs express in decimals or 7 yrs and 1 month.
From Mauritius
From Mauritius
Community Support and Knowledge-base on business, career and organisational prospects and issues - Register and Log In to CiteHR and post your query, download formats and be part of a fostered community of professionals.
ADD.jpg
Form 2 for family add.pdf