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
From India, Ranchi
77You 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
From India, Ranchi
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..
From Saudi Arabia, Jeddah
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..
From Saudi Arabia, Jeddah
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.
From India, Mumbai
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.
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
From India, Kolkata
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
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.
From India, Mumbai
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.
From India, Madras
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.
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
From India, Bangalore
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
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.
From India, Mumbai
(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.
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.
From India, Mumbai
(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.
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'
From India, Delhi
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'
From India, Delhi
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