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(0)
Amend(0)
3From India, Mumbai
Acknowledge(0)Amend(0)
Dear Mr. Dattatraycitehr,
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 shall automatically get the addition of both numbers.
From India, Visakhapatnam
Acknowledge(0)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 shall automatically get the addition of both numbers.
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.
Eswar
From India, Chennai
Acknowledge(0)Amend(0)
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.
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.
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 =ROUNDOWN(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
From India, Jodhpur
Acknowledge(0)Amend(0)
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 =ROUNDOWN(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
From India, Jodhpur
Acknowledge(0)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(0)Amend(0)
Cell C1 = 62
Cell C2 = 23
Cell C3 = (C1 + C2) / 12, i.e., 7.1 years.
From India, Mumbai
Acknowledge(0)Amend(0)
For getting the number of days, years, or months between two dates, use the DATEDIF function.
Suppose cell A1 contains 01-01-2010 and B1 contains 01-12-2010. Then use "=DATEDIF(A1,B1,"Y")" to get the number of years between two dates. Replace "Y" with "M" or "D" to get the number of months or days, respectively.
If you have more questions about Excel automation in the HR area, you can post them to me.
Regards, M K Soni
From India, Kolkata
Acknowledge(0)Amend(0)
Suppose cell A1 contains 01-01-2010 and B1 contains 01-12-2010. Then use "=DATEDIF(A1,B1,"Y")" to get the number of years between two dates. Replace "Y" with "M" or "D" to get the number of months or days, respectively.
If you have more questions about Excel automation in the HR area, you can post them to me.
Regards, M K Soni
From India, Kolkata
Acknowledge(0)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(0)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(0)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)
Hi,
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)
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)
Dear Dattatraycitehr,
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 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 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 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)
Dear Dattatraycitehr,
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 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 given 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,
[Your Name]
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 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 given 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,
[Your Name]
From India, Ranchi
Acknowledge(0)Amend(0)
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 a decimal point by using 12 as the denominator. Add, then round off the answer to 1 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 2 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(0)Amend(0)
However, I think that is wrong. Getting the age based on the given data will simply add the 2 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(0)Amend(0)
Dear Dattatray,
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.
Now, 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(0)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.
Now, 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(0)Amend(0)
Probably, 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.
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.
Manoj
From India, Kolkata
Acknowledge(0)Amend(0)
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.
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.
Manoj
From India, Kolkata
Acknowledge(0)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)
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:
a) OT hours below 10 minutes are treated as zero.
b) 10 minutes 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.
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)
In our company, for calculating OT wages, I devised a formula:
a) OT hours below 10 minutes are treated as zero.
b) 10 minutes 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.
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)
Dear Dattatray,
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.
If the data is in the format as mentioned in (2) 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.
The explanation:
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(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.
If the data is in the format as mentioned in (2) 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.
The explanation:
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(0)Amend(0)
Dear Dattatray,
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:
In the year cell: A1 + A2 + quotient((B1 + B2), 12) {result: 7}
In the month cell: mod((B1 + B2), 12) {result: 1}
Hopefully, this will help you.
From India, Mumbai
Acknowledge(0)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:
In the year cell: A1 + A2 + quotient((B1 + B2), 12) {result: 7}
In the month cell: mod((B1 + B2), 12) {result: 1}
Hopefully, this will help you.
From India, Mumbai
Acknowledge(0)Amend(0)
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
Acknowledge(0)Amend(0)
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
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(0)Amend(0)
From India, Madras
Acknowledge(0)Amend(0)
Dear Dattatraya,
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.
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.
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)
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.
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.
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 I am 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.
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.
From India, Mumbai
Acknowledge(0)Amend(0)
Sorry for not replying, sir. Generally, I use the internet in the morning only because I am an IT student as well as I am 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.
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.
From India, Mumbai
Acknowledge(0)Amend(0)
Find the below formula:
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(0)Amend(0)
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(0)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)Gathering data for an AI comment.... Sending emails to relevant members...
Join Our Community and get connected with the right people who can help. Our AI-powered platform provides real-time fact-checking, peer-reviewed insights, and a vast historical knowledge base to support your search.
Mobile codes excel 2nd addition.xls
Advanced Excel 2003.pdf
152120-very-use ful-hr-manageme nt-software-bui lt-excel-psa-hr -manager-mainta in-payrrol-exce l-soft.zip