How to calculate age in Excel (with steps and functions)

By Indeed Editorial Team

Published 2 May 2022

The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.

Excel is a program that offers a wide variety of tools that can help you calculate and manage various data. Sometimes, it's helpful to use Excel to calculate ages or term lengths when working on personal and professional tasks. Learning this skill can help you complete your objectives while allowing you to gain more experience with Excel formulas. In this article, we explain how to calculate age in Excel and explore some situations where you may find this useful.

Some reasons to learn how to calculate age in Excel

It's helpful to learn how to calculate age in Excel because this method is often faster than performing manual calculations. You can apply it to various data sets. Calculating age in Excel often involves finding the difference between two dates and representing them as days, months or years. For example, some expecting parents like to calculate and track the various milestones of their pregnancy to prepare for their new arrival. A business may want to calculate the age of their insurance policies to make sure they remain up-to-date and valid.

Knowing how to calculate different ages and term lengths can help you when applying for a job. Some employers may favour candidates who demonstrate the ability to use functions and create formulas in spreadsheet applications. It's often a requirement in accounting, finance and analysis positions.

Related: Computer skills: definitions and examples

How to calculate age in Excel

You can learn to calculate age in Excel by reviewing the following steps:

1. Open the Excel document

The first step typically involves opening the Excel document. You can open your file by clicking on the 'Microsoft Office' button and choosing 'Microsoft Excel' from the menu or by clicking on an independent icon for Excel. If you have multiple Excel documents saved to your computer, you can double click on the one you want to open, or you can choose a new document.

2. Change the date format

Next, you can add the dates that make the most sense for your calculations. For instance, if you want to calculate dates that include the current day, you can enter the current date as the end date and a previous date as the start date. Typically, the date format is 'DD/MM/YYYY'. If you want to calculate someone's age, you can enter their birthday using the above format in one of the cells and add the current date to the adjacent column along the same row. You can add as many birthdates as you like to use them in your calculations.

3. Enter the month or year formula

Once you enter your dates, you can calculate the age in years and months. You can use Excel to take the difference between the dates and divide the result by one month or by 365 days. The following formula calculates how many years old someone is:

'=INT ((B2-A2)/365)'

You can calculate the months using this formula:

'=B2-INT ((B2-A2)/30.4)'

In both formulas, B2 represents the current or most recent date and A2 represents the starting date. If you want to make calculations for more than one birthday, you can replace A2 and B2 with the corresponding date ranges.

4. Calculate the age

Once Excel accepts the formula you entered, it typically shows you the finished calculation. For example, if you enter the formula into C2 without any errors, you can see the age you wanted to calculate reflected in either years or months, based on the formula. Often these numbers appear without written units. For example, if your calculations return the number 20, you can determine whether the person is 20 years old or 20 months old, depending on which formula you used. You can perform these steps for each person whose age you wish to know.

Functions for calculating age in Excel

Here are some functions you can use when calculating age:

DATEDIF function

DATEDIF is a function in Excel that compares two dates and returns the difference between them. Typically, you use the DATEDIF clause when checking for a condition. For instance, if you want to find out the age of a particular date in Excel, you can use the DATEDIF formula. If 'A2' has the date '12/01/2000' and B2 has the date '12/01/2020', you can insert the formula '= DATEDIF (A2, B2, "Y")' into C2. When you press 'Enter' on your keyboard, it often displays the difference in years.

The DATEDIF function usually includes a start date and end date so that it works properly. A unit can also be a part of the function. The unit represents how you want the results to appear. For example, 'Y' tends to represent years, and 'M' tends to represent months. The end date is usually later than the start date. If the end date isn't, the function often returns the '#NUM' error.

Related: 15 basic Excel formulas to learn (with examples)

YEARFRAC function

YEARFRAC works similarly to DATEDIF, but it only outputs a fractional year. Using the YEARFRAC function can help you measure the date range by a fraction. YEARFRAC has three arguments. The first argument is the start date, the second is the stop date and the third is the basis. The basis refers to the number of days the function counts while calculating the fractional years. Typically the YEARFRAC function uses a fraction between the first two arguments to make its calculations.

For example, if you want to display how old you are as a fraction, the first argument would be January 1st, while the last one would be December 31st. The formula would be '= YEARFRAC (B2, A2,1)'. For this formula numerals often work best.

YEARFRAC and INT functions

You can also YEARFRAC with INT (an integer) to calculate age in Excel. The syntax to follow is '=INT (YEARFRAC (cell, TODAY ()))'. This function returns a difference based on years. For instance, if cell 'A1' has the date '12/01/2009' and cell 'B1' has the current date, when you insert the formula '=INT (YEARFRAC (B1, TODAY ()))' into C1it typically returns the difference of years between the two dates as an integer.

ROUNDDOWN function

ROUNDDOWN is a function you can apply if you have a non-integer and want it to be an integer. This function is the opposite of the round-up function (ROUNDUP). When rounding down to a certain number, you typically eliminate any numbers after the decimal point. When calculating age, you can use the ROUNDDOWN function if the resulting age appears as a decimal. For example, if cell 'A1' has a start date and cell 'B1' has an end date, the function is '=ROUNDDOWN ((B1-A1)/362.25,0)'. After using this function, an age may go from 15.32 years to 15 years.

Scenarios where calculating age is helpful

Here are some circumstances where understanding how to calculate age can prove helpful:

Planning for birthdays

You may want to calculate how old a friend or family member is on their birthday. Instead of taking the time to write out the calculation on paper, you can insert the dates into Excel and quickly determine that person's age. For example, if a person was born on 1st January 2000 and their birthday is upcoming, you can easily determine their age in days, months and years using the spreadsheet formulas.

Related: FAQ: should you include your date of birth on your CV?

Determining a company's age

You may want to keep track of different company's ages to help you determine their experience level or set deadlines. You can calculate a company's age by inserting the company's opening date in the 'start date' or A2 cell and either the current date or date relevant to your inquiry in the 'end date' or B2 cell to determine the age of the company. You can use the following formula to calculate an organisation's age:

'=INT ((B2-A2)/365)'

Calculating the length of employment

You can also calculate the length of your employment at a certain company using these formulas. Once you enter your starting date and ending date, you can count the number of years, months, or days you've worked. This can help you keep track of your schedule and can help you calculate the number of days that count towards your retirement. It can also help you keep track of days between pay periods and the maximum number of days you can use for your work holiday.

Finding the age of an insurance policy

You can also use these formulas to calculate the time left on your life insurance policies to help you remember to renew them. You can set this up so that the start date represents the day the policy began, and the end date represents the present day. For example, you can use the following formula to help determine whether your life insurance policy is still valid:

'=INT (YEARS (B5)/365)'

It's important to enter this formula for cell 'B5' according to the formula above and copy it and paste it on other cells.

Please note that none of the companies, institutions or organisations mentioned in this article are affiliated with Indeed.

Related:

  • How to make a graph in Excel: a comprehensive guide


Explore more articles