Malay.kumar1
Hr & Administrtion
Praveenpathak538
Manager-corporate Hr
Manojsoni78
Senior Manager
+11 Others

Thread Started by #Dattatraycitehr

Hi friends, I have two excel cell;first excel cell contain 5.2 Years and second cell contain 1.11 years.Now my question is that how to add these two cells so that i can get answer 7.1 years
7th January 2013 From India, Mumbai
Dear Mr. Dattatraycitehr,
SUM in excel is quiet simple, if you want the sum of both you need to try this - In a excel box type "=5.2+1.11" and press enter. You shall automatically get the addition of both numbers.
7th January 2013 From India, Visakhapatnam
#Anonymous
Hi
This is basic and simple step in the excel sheet in any cell type this =5.2+1.11 then you will get the answer.
One request before posted please take help from online.
It is simple even school students will do.
Eswar
7th January 2013 From India, Chennai
It is not that simple as thought by answerer. The querist numbers are in year and month and not the numbers in decimal. 5 Years and 2 months and 1 Year 11 months gets one 7 Years and 1 month.
If the querist follow what has been suggested then answer would be 5.2+1.11=6.31 and not 7 Years and 1 month. In such cases the number in cell must be fed like =5+2/12 and 1+11/12. The answer in the result shall shall be 7.083333 or 7+1/12
If the querist wants the answer in year and month then he should write like this
Cell A1 - 5, Cell B1 - 2, Cell C1 - 1, Cell D1 - 11
In cell E1 - The formula should be =A1+B1/12+C1+D1/12
In Cell F1 - The formula should be =rounddown(E1,0)
In Cell G1 - The formula should be =(E1-D1)*12
A1 B1 C1 D1 E1 F1 G1
5 2 1 11 7.0833 7 1
The answer will be Years (F1) and Months (G1)
You may hide cell E1, If you wish
Jawaharlal Moondra
9829028028
8th January 2013 From India, Jodhpur
Why do you making this more complicated fist 5.1 years and 1.11 years convert them in months i.e. 62 months and 23 months and add both cell. Result divide by 12.
Cell C1 =62 Cell C2=23 Cell C3=(C1+C2)/12 i.e.7.1 Years
8th January 2013 From India, Mumbai
For getting number of days, years or month between two dates, use DATEDIF function.
Suppose if cell A1 contains 01-01-2010 and B1 contains 01-12-2010, then use "=DATEDIF(A1,B1,"Y")" THIS WILL GIVEN NUMBER OF YEARS between two dates. Replace "y" with "m"or "d", to get number of days also.
If you have more question about excel automation in HR area you can post question to me.
Regards,
M K Soni
8th January 2013 From India, Kolkata
The important thing is, the answer should be 7.1 years.
You are advising to add 62+ 23 and divide the result by 12 to get the answer.
But how did you arrive at 62 & 23. If it one or two cell, you can do this manually. If the excel sheet is long and needs the total to be filled in by formula, you can't do that manually.
If you simply multiply by 12, you will get (5.1 * 12 = 61.2 months) which should actually be 61 months. and similarly 1.11 * 12 =13.32 which in fact should be 23 months
8th January 2013 From India, Madras
hii mam, its not solution what i want? actually i want answer 7.1 Years bt using ur formula i get answer 6.31 which i hv tried before posting this query on this site.
8th January 2013 From India, Mumbai
Hi,
You can never get 7.1 years with the values you have posted. 5.21 if converted in months is 61.2 and 1.11 is 13.32, the total is 74.52, which by no means can make 7.1 years. as it is equal to 85.2 months. Please review your question to get the correct answer.
8th January 2013 From India, Hyderabad
#Anonymous
Dear Dattatraycitehr,
You can use previous Exp. of Individuals in months like that individuals have 3.2 months previous experience then you can enter 38 months and it's not changeable, in another cell that contains Exp. in your company you will calculate using this formula
=round(Today()-Date of Joining cell address)/30,0)
i.e. the date of joining of Individual 1-Apr-2007 this formula will return you 70 months.
When you calculate Total Exp. of individual that time you create two separate column first will contain years and second will contain months and the formula for calculating years and months provided for our friends given below respectively
For Years =int((cell reference that contains previous exp.+cell value that contains in your organization exp.)/12)
For Months=(total exp.+in your company exp.)-12*Years
Ex.
Date of Joining Previous Experience (Months) in ur co. Experience (Months) Total Exp.
Years Months
1-Apr-07 38 70 9 0
8th January 2013 From India, Ranchi
Dear Dattatraycitehr,
You can use previous Exp. of Individuals in months like that individuals have 3.2 months previous experience then you can enter 38 months and it's not changeable, in another cell that contains Exp. in your company you will calculate using this formula
=round(Today()-Date of Joining cell address)/30,0)
i.e. the date of joining of Individual 1-Apr-2007 this formula will return you 70 months.
When you calculate Total Exp. of individual that time you create two separate column first will contain years and second will contain months and the formula for calculating years and months provided for our friends given below respectively
For Years =int((cell reference that contains previous exp.+cell value that contains in your organization exp.)/12)
For Months=(total exp.+in your company exp.)-12*Years
Ex.
Date of Joining Previous Experience (Months) in ur co. Experience (Months) Total Exp.
Years Months
1-Apr-07 38 70 9 0
8th January 2013 From India, Ranchi

Attached Files
Membership is required for download. Create An Account First
File Type: xls year calculation.xls (17.0 KB, 133 views)

Adding 2 cells directly is not possible
What you can do is to separate the year and months in different cells.
Then convert the month into decimal point by using 12 as denominator.
Then add, then round off the answer into 1 decimal place to get that 7.1
Please see attached table as example.
This is the way to get 7.1 based on your question.
But I think that is wrong, getting the age based on the given data will simply add the 2 cells, answer will be 6.31 years then convert 0.31 into whole number to get the number of months (3.72 or if rounded off will be 4 months) = 6 years and 4 months..
8th January 2013 From Saudi Arabia, Jeddah

Attached Files
Membership is required for download. Create An Account First
File Type: pdf Adding Years & Months in Excel.pdf (169.5 KB, 71 views)

Dear Dattatray,
I assume that data are large in volume and you don't want to re do. Further one cell carries Yr.MM and you want to add a number of such cells .
Now
(1) The format used by you is in error because for 1yr 10 month, excel read data as 1.1 and for 1 yr 1 month also it is 1.1 . Ideally it should be 1.01 and 1.10. If it can be done, rest of job is very easy. Please revert back.
8th January 2013 From India, Mumbai
Probly this will solve your problem...
in A1 type 5.1
in b1 type 1.11
type formula =+(((ROUND(a1,0))*12)+(a1-INT(a1))*10)/12 in c1
type formula =+(((ROUND(B1,0))*12)+(B1-INT(B1))*100)/12
FIRST FORMULA WILL GIVE YOU RESULT 5.17
NEXT FORMUAL WILL GIVE YOU RESULT 1.92
NOW SUM THIS TO GIVE YOU 7.09
ROUND IT to single decimal to arrive at 7.1
Note that if the decimal is only one digit, i have multiplied by 10 and if it is two decimal then it should be multiplied by 100.
Hope this will solve your problem
Manoj
8th January 2013 From India, Kolkata
Thanks Manoj soni your solution is so helpful and so easy to understand thanks Manoj,and may be i am going to use your solution if i do not get anything from my side.
8th January 2013 From India, Mumbai
Hi, I was trying various ways to find a solution to the subject query. Try the following:

In our Company, for calculating OT wages, I devised a formula; viz, a) OT hr below 10 minutes is taken as Zero. b) 10 Minutes to 15 minutes is taken as 15 minutes OT. This is recorded in the system as 0.25 Hr.

Similarly 30 minutes OT is recorded as 0.50 Hr., 45 Minutes OT as 0.75 Hr.

I have OT Rate stored in a Cell and OT hrs calculated as above is multiplied by OT Rate and recorded in a different cell as OT wages.

------------------------------------------------------------------------------------------------------------------

Similarly, you may record the YEAR in A1, MONTHS in B1,

C1 will be total number of months; i.e. = (A1 * 12 ) + B1

D1 will be TOTAL YEARS = C1/12

In your queried case, A1 will be 5, B1 will be 2, C1 will be 62 and D1 will be 62/12 = 5.16666

A2 will be 1, B2 will be 11 and C2 will be 23 and D2 will be 23/12 = 1.9166

The Total of D1 + D2 will be 7.0832 ROUNDED OFF TO 7.1 years, the desired answer.
8th January 2013 From India, Madras
Dear Members,
the first part of above i.e till capturing year and month in separate columns is perfect.
But dividing total number of months by 12 will not give us the accurate result.
For eg: 23 (months) / 12 will give a result of 1.917; after rounding off, the result will be 1 year 9 months instead of 1 year 11 months. I strongly feel that the only way out is to make use of the macros. ie, writing a programme in VB and storing in macros. Any Macro specialists in our group who can extend help??
Boby C Augustine
Mangalore
Karnataka
8th January 2013 From India, Bangalore
Dear Dattatray,
(1) If you are writing year and month in same cell using dot (.) as separator , excel cannot differentiate between 1.1 ( one year one month ) and 1.10 (one year ten month.
(2) If data volume is less , it is advisable to reformat the cells so that for one year one month data entered is 1.01 and for one year ten month data entered is 1.10.
(3) If data is in format as mentioned in (2) above and 5.02 in cell A1 and 1.11 in cell A2 at the cell where sum is required write following formula
= int(a1)+int(a2)+quotient((right(a1,2)+right(a2,2)) ,12) +( mod((right(a1,2)+right(a2,2)),12))/100 .
The explanation
int(a1) = 5, int(a2) = 1, right(a1,2) = 02, right(a2,2)=11 and their sum will be 13 . quotient of 13 divided by 12 will give 1 and
mod of 13 divided by 12 and whole divided by 100 will give0 .01 . Together 7.01.
8th January 2013 From India, Mumbai
Dear Dattatray
(1) If you can use 2 cells together, one for year and one for month it will make job further easier.
(2) Say A1= 5, B1 =2 and A2= 1 and B2 = 11
(3) Then result write following formula
In the year Cell=A1+A2+quotient((B1+B2),12) { 7 }
In the month cell= mod((b1+b2),12) { 1 }
Hopefully it will help you.
8th January 2013 From India, Mumbai
Hi Dattatray, The solution should be as follows;-
The years and months will have to be in separate adjoining cells, say A1:5; B1:2 (5 years and 2 months) and A2:1; B2:11 (2 years and 11 months)
The formula to be used has to be based on logic. Hence:-
B3 will have: =IF(B1+B2>12,B1+B2-12,B1+B2)
A3 will have: = IF(B1+B2>12,A1+A2+1,A1+A2)
A B
1 5 1
2 2 11
3 = IF(B1+B2>12,A1+A2+1,A1+A2) =IF(B1+B2>12,B1+B2-12,B1+B2)

I hope that solves the problem?
Good luck.
Colonel Gahlot
'TRURECRUIT'
9th January 2013 From India, Delhi
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.
9th January 2013 From India, Madras

Attached Files
Membership is required for download. Create An Account First
File Type: zip Diff.zip (7.1 KB, 32 views)

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.
9th January 2013 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.
9th January 2013 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.
9th January 2013 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.
9th January 2013 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.
9th January 2013 From India, Mumbai

Attached Files
Membership is required for download. Create An Account First
File Type: xls Book1.xls (17.5 KB, 33 views)

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.
9th January 2013 From Mauritius
Hello,
I have completed 66 Months of service, My basic is 18000/- please clear the gratuity service & Amount will be get.
3rd August 2017 From India, Delhi
Reply (Add What You Know) Start New Discussion

Cite.Co - is a repository of information created by your industry peers and experienced seniors. Register Here and help by adding your inputs to this topic/query page.
Prime Sponsor: TALENTEDGE - Certification Courses for career growth from top institutes like IIM / XLRI direct to device (online digital learning)





About Us Advertise Contact Us
Privacy Policy Disclaimer Terms Of Service



All rights reserved @ 2019 Cite.Co™