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
From India, Mumbai
Acknowledge(2)
Amend(0)
3From India, Mumbai
Acknowledge(2)Amend(0)
Sum in Excel is quite simple. If you want the sum of both, you need to try this: In an Excel box, type "=5.2+1.11" and press enter. You will automatically get the addition of both numbers.
Regards
From India, Visakhapatnam
Acknowledge(0)Amend(0)
Regards
From India, Visakhapatnam
Acknowledge(0)Amend(0)
Hi, this is a basic and simple step in the Excel sheet. In any cell, type this formula: =5.2+1.11, then you will get the answer. One request before posting: please seek help online. It is simple; even school students can do it.
Regards,
Eswar
From India, Chennai
Acknowledge(0)Amend(0)
Regards,
Eswar
From India, Chennai
Acknowledge(0)Amend(0)
It is not as simple as thought by the answerer. The querist's numbers are in years and months, not in decimal numbers. 5 years and 2 months, and 1 year 11 months together make 7 years and 1 month. If the querist follows what has been suggested, then the answer would be 5.2 + 1.11 = 6.31, not 7 years and 1 month. In such cases, the numbers in the cell must be entered as =5 + 2/12 and 1 + 11/12. The result will be 7.083333 or 7 + 1/12.
Steps to Calculate Years and Months
If the querist wants the answer in years and months, 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 - F1) * 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.
Regards,
Jawaharlal Moondra
[Phone Number Removed For Privacy Reasons]
From India, Jodhpur
Acknowledge(6)Amend(0)
Steps to Calculate Years and Months
If the querist wants the answer in years and months, 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 - F1) * 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.
Regards,
Jawaharlal Moondra
[Phone Number Removed For Privacy Reasons]
From India, Jodhpur
Acknowledge(6)+1 more
Amend(0)
Simplifying the Calculation
Why are you making this more complicated? First, take 5.1 years and 1.11 years, convert them into months, i.e., 62 months and 23 months, and add both cells together. Then, divide the result by 12.
Cell C1 = 62
Cell C2 = 23
Cell C3 = (C1 + C2) / 12, i.e., 7.1 years.
From India, Mumbai
Acknowledge(5)
Amend(0)
Why are you making this more complicated? First, take 5.1 years and 1.11 years, convert them into months, i.e., 62 months and 23 months, and add both cells together. Then, divide the result by 12.
Cell C1 = 62
Cell C2 = 23
Cell C3 = (C1 + C2) / 12, i.e., 7.1 years.
From India, Mumbai
Acknowledge(5)Amend(0)
The crucial point is that the answer should be 7.1 years. You are advised to add 62 + 23 and divide the result by 12 to get the answer. However, how did you arrive at 62 & 23? If it is one or two cells, you can do this manually. If the Excel sheet is long and requires the total to be filled in by a 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. Similarly, 1.11 * 12 = 13.32, which in fact should be 23 months.
From India, Madras
Acknowledge(1)
Amend(0)
If you simply multiply by 12, you will get (5.1 * 12 = 61.2 months), which should actually be 61 months. Similarly, 1.11 * 12 = 13.32, which in fact should be 23 months.
From India, Madras
Acknowledge(1)Amend(0)
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.
From India, Mumbai
Acknowledge(0)Amend(0)
From India, Mumbai
Acknowledge(0)Amend(0)
You can never get 7.1 years with the values you have posted. 5.21, if converted to 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.
From India, Hyderabad
Acknowledge(0)Amend(0)
From India, Hyderabad
Acknowledge(0)Amend(0)
You can use the previous experience of individuals in months. For example, if individuals have 3.2 months of previous experience, then you can enter 38 months, and it's not changeable. In another cell that contains experience in your company, you can calculate using this formula:
=ROUND((TODAY() - Date of Joining cell address) / 30, 0)
For instance, if the date of joining of an individual is 1-Apr-2007, this formula will return 70 months. When calculating the total experience of an individual, you can create two separate columns. The first will contain years, and the second will contain months. The formulas for calculating years and months are provided for our friends below, respectively:
For Years: =INT((cell reference containing previous experience + cell value containing experience in your organization) / 12)
For Months: (total experience in your company) - 12 * Years
Example:
Date of Joining | Previous Experience (Months) | in your co. Experience (Months) | Total Exp. | Years | Months
1-Apr-07 | 38 | 70 | 9 | 0
Kindly review and implement these calculations for accurate results.
Thank you!
From India, Ranchi
Acknowledge(0)Amend(0)
=ROUND((TODAY() - Date of Joining cell address) / 30, 0)
For instance, if the date of joining of an individual is 1-Apr-2007, this formula will return 70 months. When calculating the total experience of an individual, you can create two separate columns. The first will contain years, and the second will contain months. The formulas for calculating years and months are provided for our friends below, respectively:
For Years: =INT((cell reference containing previous experience + cell value containing experience in your organization) / 12)
For Months: (total experience in your company) - 12 * Years
Example:
Date of Joining | Previous Experience (Months) | in your co. Experience (Months) | Total Exp. | Years | Months
1-Apr-07 | 38 | 70 | 9 | 0
Kindly review and implement these calculations for accurate results.
Thank you!
From India, Ranchi
Acknowledge(0)Amend(0)
You can use the previous experience of individuals in months. For example, if an individual has 3.2 months of previous experience, you can enter 38 months, and it's not changeable. In another cell that contains the experience in your company, you can calculate using this formula:
=ROUND((TODAY() - Date of Joining cell address) / 30, 0)
For instance, if the date of joining of an individual is 1-Apr-2007, this formula will return 70 months.
Calculating Total Experience
When calculating the total experience of an individual, you should create two separate columns. The first column will contain years, and the second will contain months. The formulas for calculating years and months are provided for our friends below, respectively:
For Years: =INT((cell reference containing previous experience + cell value containing experience in your organization) / 12)
For Months: (total experience + experience in your company) - 12 * Years
Example:
Date of Joining | Previous Experience (Months) | Experience in your Company (Months) | Total Experience | Years | Months
1-Apr-07 | 38 | 70 | 9 | 0
Please ensure the accurate application of these formulas for calculating the total experience of individuals.
Thank you
Regards
From India, Ranchi
Acknowledge(2)Amend(0)
=ROUND((TODAY() - Date of Joining cell address) / 30, 0)
For instance, if the date of joining of an individual is 1-Apr-2007, this formula will return 70 months.
Calculating Total Experience
When calculating the total experience of an individual, you should create two separate columns. The first column will contain years, and the second will contain months. The formulas for calculating years and months are provided for our friends below, respectively:
For Years: =INT((cell reference containing previous experience + cell value containing experience in your organization) / 12)
For Months: (total experience + experience in your company) - 12 * Years
Example:
Date of Joining | Previous Experience (Months) | Experience in your Company (Months) | Total Experience | Years | Months
1-Apr-07 | 38 | 70 | 9 | 0
Please ensure the accurate application of these formulas for calculating the total experience of individuals.
Thank you
Regards
From India, Ranchi
Acknowledge(2)Amend(0)
Adding two cells directly is not possible. What you can do is separate the year and months into different cells. Then convert the month into a decimal point by using 12 as the denominator. Add them, then round off the answer to one decimal place to get 7.1. Please see the attached table as an example. This is the way to get 7.1 based on your question.
However, I think that is wrong. Getting the age based on the given data will simply add the two cells. The answer will be 6.31 years. Then convert 0.31 into a 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
Acknowledge(2)
Amend(0)
However, I think that is wrong. Getting the age based on the given data will simply add the two cells. The answer will be 6.31 years. Then convert 0.31 into a 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
Acknowledge(2)Amend(0)
I assume that the data is large in volume and you don't want to redo it. Furthermore, each cell carries Yr.MM, and you want to add a number of such cells.
Format Adjustment for Excel Cells
The format used by you is incorrect because for 1 year 10 months, Excel reads the data as 1.1, and for 1 year 1 month, it also shows as 1.1. Ideally, it should be 1.01 and 1.10. If this adjustment can be made, the rest of the task is very easy. Please revert back.
Thank you.
From India, Mumbai
Acknowledge(1)Amend(0)
Format Adjustment for Excel Cells
The format used by you is incorrect because for 1 year 10 months, Excel reads the data as 1.1, and for 1 year 1 month, it also shows as 1.1. Ideally, it should be 1.01 and 1.10. If this adjustment can be made, the rest of the task is very easy. Please revert back.
Thank you.
From India, Mumbai
Acknowledge(1)Amend(0)
Excel Formula for Adding Years
Probably, this will solve your problem. In A1, type 5.1. In B1, type 1.11.
Type the formula =+(((ROUND(A1,0))*12)+(A1-INT(A1))*10)/12 in C1.
Type the formula =+(((ROUND(B1,0))*12)+(B1-INT(B1))*100)/12.
The first formula will give you the result 5.17. The next formula will give you the result 1.92.
Now sum these to give you 7.09. Round it to a single decimal to arrive at 7.1.
Note that if the decimal is only one digit, I have multiplied it by 10, and if it is two decimals, then it should be multiplied by 100.
Hope this will solve your problem.
Regards,
Manoj
From India, Kolkata
Acknowledge(2)
Amend(0)
Probably, this will solve your problem. In A1, type 5.1. In B1, type 1.11.
Type the formula =+(((ROUND(A1,0))*12)+(A1-INT(A1))*10)/12 in C1.
Type the formula =+(((ROUND(B1,0))*12)+(B1-INT(B1))*100)/12.
The first formula will give you the result 5.17. The next formula will give you the result 1.92.
Now sum these to give you 7.09. Round it to a single decimal to arrive at 7.1.
Note that if the decimal is only one digit, I have multiplied it by 10, and if it is two decimals, then it should be multiplied by 100.
Hope this will solve your problem.
Regards,
Manoj
From India, Kolkata
Acknowledge(2)Amend(0)
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
Acknowledge(0)Amend(0)
From India, Mumbai
Acknowledge(0)Amend(0)
I was trying various ways to find a solution to the subject query. Try the following:
Calculating Overtime Wages
In our company, for calculating overtime (OT) wages, I devised a formula:
• OT hours below 10 minutes are treated as zero.
• 10 to 15 minutes is considered as 15 minutes of OT, recorded in the system as 0.25 hours.
Similarly, 30 minutes of OT is recorded as 0.50 hours, and 45 minutes of OT as 0.75 hours.
I have the OT rate stored in a cell, and the OT hours calculated as above are multiplied by the OT rate and recorded in a different cell as OT wages.
Calculating Total Years from Months
Similarly, you may record the year in A1 and months in B1.
C1 will represent the total number of months, calculated as: (A1 * 12) + B1.
D1 will indicate the total years, calculated as C1/12.
For your given 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, 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, which is the desired answer.
From India, Madras
Acknowledge(0)Amend(0)
Calculating Overtime Wages
In our company, for calculating overtime (OT) wages, I devised a formula:
• OT hours below 10 minutes are treated as zero.
• 10 to 15 minutes is considered as 15 minutes of OT, recorded in the system as 0.25 hours.
Similarly, 30 minutes of OT is recorded as 0.50 hours, and 45 minutes of OT as 0.75 hours.
I have the OT rate stored in a cell, and the OT hours calculated as above are multiplied by the OT rate and recorded in a different cell as OT wages.
Calculating Total Years from Months
Similarly, you may record the year in A1 and months in B1.
C1 will represent the total number of months, calculated as: (A1 * 12) + B1.
D1 will indicate the total years, calculated as C1/12.
For your given 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, 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, which is the desired answer.
From India, Madras
Acknowledge(0)Amend(0)
You can use the previous experience of individuals in months. For example, if an individual has 3.2 years of previous experience, you can enter 38 months, which is not changeable. In another cell that contains experience in your company, you can calculate using this formula:
=ROUND((TODAY() - Date of Joining cell address) / 30, 0)
For instance, if the date of joining of an individual is 1-Apr-2007, this formula will return 70 months.
When calculating the total experience of an individual, you should create two separate columns. The first column will contain years, and the second will contain months. The formulas for calculating years and months are provided for our friends below, respectively:
For Years:
=INT((cell reference containing previous exp. + cell value containing your organization exp.) / 12)
For Months:
(total exp. + in your company exp.) - 12 * Years
Ex.
Date of Joining Previous Experience (Months) In your co. Experience (Months) Total Exp.
Years Months
1-Apr-07 38 70 9 0
Dear Members,
The first part above, until capturing year and month in separate columns, is perfect. However, dividing the total number of months by 12 will not give us an accurate result. For example, 23 months divided by 12 will result in 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 macros, i.e., writing a program in VB and storing it in macros. Are there any Macro specialists in our group who can provide assistance?
Regards,
Boby C Augustine
Mangalore, Karnataka
From India, Bangalore
Acknowledge(0)Amend(0)
=ROUND((TODAY() - Date of Joining cell address) / 30, 0)
For instance, if the date of joining of an individual is 1-Apr-2007, this formula will return 70 months.
When calculating the total experience of an individual, you should create two separate columns. The first column will contain years, and the second will contain months. The formulas for calculating years and months are provided for our friends below, respectively:
For Years:
=INT((cell reference containing previous exp. + cell value containing your organization exp.) / 12)
For Months:
(total exp. + in your company exp.) - 12 * Years
Ex.
Date of Joining Previous Experience (Months) In your co. Experience (Months) Total Exp.
Years Months
1-Apr-07 38 70 9 0
Dear Members,
The first part above, until capturing year and month in separate columns, is perfect. However, dividing the total number of months by 12 will not give us an accurate result. For example, 23 months divided by 12 will result in 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 macros, i.e., writing a program in VB and storing it in macros. Are there any Macro specialists in our group who can provide assistance?
Regards,
Boby C Augustine
Mangalore, Karnataka
From India, Bangalore
Acknowledge(0)Amend(0)
If you are writing the year and month in the same cell using a dot (.) as a separator, Excel cannot differentiate between 1.1 (one year one month) and 1.10 (one year ten months). If the data volume is low, it is advisable to reformat the cells so that for one year one month, the data entered is 1.01, and for one year ten months, the data entered is 1.10.
Formula for Adding Years and Months in Excel
If the data is in the format as mentioned above and 5.02 is in cell A1, and 1.11 is in cell A2, at the cell where the sum is required, write the following formula:
=INT(A1) + INT(A2) + QUOTIENT((RIGHT(A1,2) + RIGHT(A2,2)), 12) + (MOD((RIGHT(A1,2) + RIGHT(A2,2)), 12))/100.
Explanation of the Formula
INT(A1) = 5, INT(A2) = 1, RIGHT(A1,2) = 02, RIGHT(A2,2) = 11, and their sum will be 13. The quotient of 13 divided by 12 will give 1, and the modulus of 13 divided by 12 and the whole divided by 100 will give 0.01. Together, it will be 7.01.
From India, Mumbai
Acknowledge(1)Amend(0)
Formula for Adding Years and Months in Excel
If the data is in the format as mentioned above and 5.02 is in cell A1, and 1.11 is in cell A2, at the cell where the sum is required, write the following formula:
=INT(A1) + INT(A2) + QUOTIENT((RIGHT(A1,2) + RIGHT(A2,2)), 12) + (MOD((RIGHT(A1,2) + RIGHT(A2,2)), 12))/100.
Explanation of the Formula
INT(A1) = 5, INT(A2) = 1, RIGHT(A1,2) = 02, RIGHT(A2,2) = 11, and their sum will be 13. The quotient of 13 divided by 12 will give 1, and the modulus of 13 divided by 12 and the whole divided by 100 will give 0.01. Together, it will be 7.01.
From India, Mumbai
Acknowledge(1)Amend(0)
If you can use two cells together, one for the year and one for the month, it will make the job even easier. For example, let's say A1 = 5, B1 = 2, A2 = 1, and B2 = 11. Then, write the following formulas:
Year Calculation
In the year cell: A1 + A2 + QUOTIENT((B1 + B2), 12) {result: 7}
Month Calculation
In the month cell: MOD((B1 + B2), 12) {result: 1}
Hopefully, this will help you.
From India, Mumbai
Acknowledge(1)Amend(0)
Year Calculation
In the year cell: A1 + A2 + QUOTIENT((B1 + B2), 12) {result: 7}
Month Calculation
In the month cell: MOD((B1 + B2), 12) {result: 1}
Hopefully, this will help you.
From India, Mumbai
Acknowledge(1)Amend(0)
The solution should be as follows:
Separate Years and Months
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 (1 year and 11 months).
Formula Logic
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)`
Example Calculation
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.
Regards,
Colonel Gahlot 'TRURECRUIT'
From India, Delhi
Acknowledge(0)Amend(0)
Separate Years and Months
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 (1 year and 11 months).
Formula Logic
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)`
Example Calculation
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.
Regards,
Colonel Gahlot 'TRURECRUIT'
From India, Delhi
Acknowledge(0)Amend(0)
When I saw this post two days ago, I thought, "Why is this person asking such a simple question?" Then, just now, I came to know that it's always a challenge to work with dates and time in Excel. In this case, you have to make Excel understand that 5.2 represents 5 years and 2 months. Once you can do this, then you can easily add.
Excel always takes fractions on the basis of full integers, not as you wish. So, when you enter 5.2 years in Excel, it means 5 years and 0.2 years, which translates to 2.4 months, not just 2 months.
That's why 5.2 multiplied by 12 gives 62.4 months, not just 62 months as you might expect.
Please understand the formulas, fractions, and dates in Excel to grasp how Excel interprets decimals. You have to write a code or use a formula that would make Excel understand that 5.2 means 5 years and 2 months. So, when we add 5.2 to 1.11, Excel should recognize to separate the integer and decimal parts, add the integers (5 + 1 = 6), and add the decimals (2 + 11 = 13). Then, convert the decimal result (13) to yy.mm format (1.1), and finally, add 1.1 to 6, resulting in 7.1.
I have tried to do the same but have not been able to consolidate it into a single formula. Please see the Excel sheet where it takes two columns, and the final result is in the third column. Once you enter all the formulas, you can hide the two columns, check the formulas, and edit them if you think you can combine them into one formula.
Your question itself is incorrect and not clear. Being an IT student, you could have clearly specified that you want Excel to interpret a number as yy.mm, with the integer representing years and the decimal representing months, and then add such cells. The direct quoting of an example confused members.
From India, Madras
Acknowledge(0)Amend(0)
Excel always takes fractions on the basis of full integers, not as you wish. So, when you enter 5.2 years in Excel, it means 5 years and 0.2 years, which translates to 2.4 months, not just 2 months.
That's why 5.2 multiplied by 12 gives 62.4 months, not just 62 months as you might expect.
Please understand the formulas, fractions, and dates in Excel to grasp how Excel interprets decimals. You have to write a code or use a formula that would make Excel understand that 5.2 means 5 years and 2 months. So, when we add 5.2 to 1.11, Excel should recognize to separate the integer and decimal parts, add the integers (5 + 1 = 6), and add the decimals (2 + 11 = 13). Then, convert the decimal result (13) to yy.mm format (1.1), and finally, add 1.1 to 6, resulting in 7.1.
I have tried to do the same but have not been able to consolidate it into a single formula. Please see the Excel sheet where it takes two columns, and the final result is in the third column. Once you enter all the formulas, you can hide the two columns, check the formulas, and edit them if you think you can combine them into one formula.
Your question itself is incorrect and not clear. Being an IT student, you could have clearly specified that you want Excel to interpret a number as yy.mm, with the integer representing years and the decimal representing months, and then add such cells. The direct quoting of an example confused members.
From India, Madras
Acknowledge(0)Amend(0)
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
Acknowledge(1)Amend(0)
From India, Madras
Acknowledge(1)Amend(0)
Noted the sarcasm in your reply to kraviravi.kravi@gmail.com. Hopefully, you are looking for a solution and not a forum for time pass.
Understanding the Problem
The problem of "adding two data" expressed in "years and months" is 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 are always of similar items, and obviously, code writers of Excel are not ignorant of this, and hence standard formulas are there to solve problems of this kind.
Framing the Right Statement
The difficulty was in framing the "right statement" for the query. There are more than one ways of extracting information from the "wrong format" and putting it in the correct format, adding them, and reconverting back to the "wrong format." That is why I asked whether you are willing to forego the "wrong format" of writing years and months in one cell. But no response.
Again, I am repeating, the problem of "adding two data" expressed in "years and months" is very simple. You can make it simpler by formatting correctly.
From India, Mumbai
Acknowledge(0)Amend(0)
Understanding the Problem
The problem of "adding two data" expressed in "years and months" is 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 are always of similar items, and obviously, code writers of Excel are not ignorant of this, and hence standard formulas are there to solve problems of this kind.
Framing the Right Statement
The difficulty was in framing the "right statement" for the query. There are more than one ways of extracting information from the "wrong format" and putting it in the correct format, adding them, and reconverting back to the "wrong format." That is why I asked whether you are willing to forego the "wrong format" of writing years and months in one cell. But no response.
Again, I am repeating, the problem of "adding two data" expressed in "years and months" is very simple. You can make it simpler by formatting correctly.
From India, Mumbai
Acknowledge(0)Amend(0)
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
Acknowledge(0)Amend(0)
From India, Mumbai
Acknowledge(0)Amend(0)
Dear Premendra Pranay, sorry for not replying, sir. Generally, I use the internet in the morning only because I am an IT student as well as working as an HR coordinator in one of the reputed companies. So, I do not get much time to use the internet. This morning, when I checked my mail, Ravi's post was in front of my eyes, so I checked it and replied to Ravi to appreciate and say thanks.
Solution Review
Before writing this post, I have 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 it's not so easy that anyone can solve it in a minute.
Regards.
From India, Mumbai
Acknowledge(0)Amend(0)
Solution Review
Before writing this post, I have 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 it's not so easy that anyone can solve it in a minute.
Regards.
From India, Mumbai
Acknowledge(0)Amend(0)
Formula for Adding Excel Cells
Find the formula below:
Put 5.02 in A1 and 7.11 in B1, then copy the formula below 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 Excel sheet.
From India, Mumbai
Acknowledge(2)Amend(0)
Find the formula below:
Put 5.02 in A1 and 7.11 in B1, then copy the formula below 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 Excel sheet.
From India, Mumbai
Acknowledge(2)Amend(0)
In fact, the question has been wrongly formulated. You cannot express 5 years and 2 months as 5.2 years. 0.2 is 2/10, and 2 months is 2/12, which is 0.16666. If you wanted to express 5 years and 2 months in decimals, then it should be expressed as 5.1666666. Similarly, 1 year and 11 months would be 1.92 in decimals, which would give a total of approximately 7.08 years expressed in decimals or 7 years and 1 month.
From Mauritius
Acknowledge(0)Amend(0)
From Mauritius
Acknowledge(0)Amend(0)CiteHR is an AI-augmented HR knowledge and collaboration platform, enabling HR professionals to solve real-world challenges, validate decisions, and stay ahead through collective intelligence and machine-enhanced guidance. Join Our Platform.
Mobile codes excel 2nd addition.xls
Advanced Excel 2003.pdf