How to calculate age in Excel

How to calculate age in Excel. In spreadsheets created in Microsoft Excel, users often interact with a list of employees or other individuals, collecting general information about them. This includes the date of birth, which cannot always be quickly translated into a person’s current age just by looking at the numbers.

How to calculate age in Excel

In this article, I’ll show you how to automate the calculation of a person’s age, given their date of birth and today’s date.

Preparatory actions

First, let’s figure out what you need to have in the spreadsheet to implement the task. Take a look at the following image – it’s a simple layout with employee names and their date of birth. The “Age” field remains empty for now, but in the future the result of calculations using the formula will be placed there.

It remains only to indicate the current date in one of the free cells. You can do it manually, but it’s much easier to call a special function, which will also be constantly updated to show the latest information.

To do this, find the very empty cell anywhere in the table, declare the function = TODAY () in it and press Enter to apply the changes.

You do not need to enter any values ​​in the quotes themselves, the function will work correctly anyway. Pay attention to the fact that right now the cell shows the date that is set in your operating system (I think it’s not worth saying that it should be correct).

If you would like to learn more about the TODAY  feature , please follow the link below and read my other article on the topic.

Create a Formula to Calculate Age in Microsoft Excel

Now let’s create the very formula that will allow you to very quickly calculate the age of a person, starting from the two available values ​​\u200b\u200b(they were discussed above). Further, this formula can be stretched to the entire length of the table and add values ​​​​when needed.

1. Select the first cell in the Age column and call the =YEARFRACTION( .
2. For clarity, let’s go to the window “Function Arguments” , so that there are no problems with choosing the appropriate cells.
3. The date of birth of the person acts as the starting date, respectively, you need to click on the cell to add it to the arguments, or you can enter its number in the field manually.
4. The end date is today, that is, you need to select the value of the TODAY function , which we declared earlier.
5. Basis is a method for calculating the day, which must be equal to one for age.
6. After entering all the information, press Enter and see the result.
7. You may see a lot of decimal places, which is not required to display the age, so open the list with Number tools and click the button to remove decimal places.
8. As you can see, all the values ​​in the table are filled in and the correct age is displayed. Now the formula can be stretched, but more on that later.

Stretching the Age Calculation Formula

If you are an advanced user of Microsoft Excel, stretching formulas to pin certain cells will not be a problem for you. You can skip this step and complete the task yourself. For those who have not previously performed such an operation, I recommend that you read the following instructions. However, first I will say that you can stretch TODAY to the entire column so as not to freeze the cell. 

However, displaying the same date on a whole column at least spoils the aesthetic appearance of the table, and at the very least, it is not always possible. Therefore, it is better to pin the cell, which is done like this:

1. Select the field with the age calculation formula, and add $ signs between the letter and number of the end date cell to indicate a constant. This will freeze the entire cell, not just the column or row. As a result, the number will look, for example, as $A$1 .
2. Then hold down the left mouse button at the right border of the cell and drag it to the full length of the table until the end of the list of people.
3. Make sure all ages are displayed correctly. If not, double-check that you have secured the cell correctly.

In conclusion, I note that when all decimal places are removed, the age will be rounded up if less than six months are left before the birthday. If you want to avoid this, leave one decimal place. Then the type of value will be as shown in the following image.

There is nothing difficult in calculating age in Excel, since the program has all the necessary functions for this, which also have a very simple syntax. I hope you have seen this for yourself.