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.
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.
1 Attachment(s) [Login To View]