How to Calculate Age in Excel

Being able to calculate age in Excel is a very useful and underrated skill to possess. It not only helps you learn more about your team and customers, but also helps your organization with everything from allocating resources properly, to succession planning, and more.

While calculating age in Excel might seem complex on the surface, it’s a relatively simple process. This guide is going to take you through the steps of how to do it properly and go over some of the business applications associated with calculating age in Excel.

How to Properly Calculate Age in Excel

To calculate age from date of birth in Excel, all you need to do is use the DATEDIF function. This function calculates the difference between two dates, which in this case would be the person’s birth date and the current date.

Once you format the cells properly, enter a basic formula, and copy this formula to the remaining cells, you’ll have a dynamic chart that’s able to instantly calculate someone’s age.

Step-by-Step Process For Calculating Age in Excel

Here’s an overview of the steps involved in quickly and easily calculating age in Excel.

1. Format Your Cells

The first step is to create the age chart and properly format your cells. Begin by creating two columns, one that’s titled “Date of Birth” and the other “Age”. In the “Date of Birth” column, feel free to enter the dates of birth you’re looking to calculate.

To properly format the “Date of Birth” cells, you need to click and drag your cursor over the entire column, right-click, and press Format Cells. Next, navigate to “Number” and choose the “Date” category. Feel free to choose whichever specific type you prefer.

After this, properly format the “Age” by performing the same click and drag. But this time, after selecting Format Cells and navigating to “Number”, choose the General category.

2. Insert the Formula

Once the chart is made and the cells are formatted, you need to add the formula to the “Age” cells. Starting with the first cell in the column, write =datedif. Next to =datedif, enter an open bracket and click the cell that contains the date of birth. After that, add a comma and enter Today, followed by an open and closed bracket.

After this, add another comma and type in “Y” (including the quotation marks) for years. Next, simply close the bracket, and you should instantly see the proper age next to the date of birth.

As a result, the formula for calculating age in Excel is: =datedif(dob,Today(),”Y”).

In this formula, dob refers to the date of birth, so where it says dob, you’ll simply click the cell that contains the date of birth you want to calculate.

3. Copy the Formula

Once this is done, you can copy this formula and apply it to the rest of the rows instantly. To do this, simply click the bottom right corner of the “Age” cell that contains the formula, and drag it over the rest of the cells in the column. The cells should automatically populate with the correct ages according to the dates of birth on their left.

Alternative Approach

In addition to this common and relatively general approach, you may also want to use functions like YEARFRAC and/or INT to get a more precise age calculation. While the method we outlined earlier quickly shows you their age in years, these other functions may provide more context to exactly how old someone is (such as being 25 and a half, or being only a few days/weeks away from turning 26).

Get in touch to learn more about how FutureSavvy’s instructor-led training programs can help you and your team!

Business Applications for Calculating Age in Excel

As we briefly touched on in the introduction, there are several reasons why being able to quickly and accurately calculate age in Excel is useful for your business.

First, it’s great for HR management as it helps you learn the age demographics of your team, helps plan for upcoming retirements to ensure you’ve got enough staff, and learn more about the skills and experience of your team to help plan your training and development.

Next, it helps you learn more about your target market and the ages of the individuals that largely make up your customer base. Age plays a major role in what people do, their pain points, and what they buy, so knowing your most common customer is great for product development and ensuring you reach the right people with your messaging.

Finally, being able to calculate ages quickly is wonderful for planning projects. Someone’s age often ties directly into how much experience they have, so knowing the age of your team ensures that you can properly allocate resources and workers where they’ll have the biggest benefit.

Also, knowing how old people are helps with succession planning to ensure projects don’t enter a stalemate when someone retires or moves to a different project.

If you want to learn more about the business applications of calculating age and how these calculations are used in a business setting, be sure to check out our dedicated guide on age calculation in Excel.

FAQs: Age in Excel

Here are a few common questions about calculating age from date of birth in Excel, along with their answers.

What is the formula for age in Excel?

The formula for calculating age in Excel is: =datedif(dob,Today(),”Y”). In this formula, dob refers to the date of birth that you want to calculate.

How do you age data in Excel?

To age data in Excel, you can use either the DATEDIF function, the INT function, or the YEARFRAC function. All of these functions help you measure the time between two dates.

What are the business benefits of calculating age in Excel?

It helps you learn more about your customers and target market, your team, and helps you allocate resources, plan for retirements, and gauge the experience level of your workers.

How do I calculate age in mm, dd, yyyy in Excel?

To calculate age in mm, dd, yyyy as opposed to dd, mm, yyyy, all you need to do is change the Locale from English (United Kingdom) to English (United Stated) when you’re formatting the cells in the “Date of Birth” column.