Adding Excel Cells with Years: How Do I Get the Correct Total?

Hi friends, I have two Excel cells; the first Excel cell contains 5.2 years, and the second cell contains 1.11 years. Now, my question is how to add these two cells so that I can get the answer 7.1 years.

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

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

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]

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.

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.

Hi mam, It's not the solution I want. Actually, I want the answer 7.1 years but using your formula, I get the answer 6.31, which I have tried before posting this query on this site.

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.

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!

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

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.

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.

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

Thanks, Manoj Soni. Your solution is so helpful and easy to understand. Thanks, Manoj, and maybe I am going to use your solution if I do not get anything from my side.

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.

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

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.

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.

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'

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.

To update, enter this formula in cell G21:
=INT(C21+B21)+INT(E21/12)&"+"&MOD(E21,12)

Then, expand the total formula across 2 cells. By following these instructions, you can proceed and reach the desired outcome.

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.

The simplest and easiest suggestion submitted seems to be to convert all to months: multiply years by 12, add, and then divide the total by 12. There is the least chance of error here.

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.

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.

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.