Calculating age is a common task that comes up in various situations, whether you’re managing employee records, analyzing customer data, or working with any other type of data that involves dates of birth.
In Excel, you can easily calculate age using built-in functions and formulas.
In this article, we’ll explore several methods to calculate age in Excel, ranging from simple formulas to more advanced approaches.
But before we get ahead of ourselves, let’s quickly understand the basics of what it entails to calculate age in Excel.
Powerful Formulas to Calculate Age in Excel
When working with date data in Excel, you’ll often encounter scenarios where you need to calculate the age of individuals or groups based on their dates of birth. However, calculating age isn’t as straightforward as simply subtracting the birth year from the current year.
There are several factors to consider, such as months and days, to ensure accurate age calculations.
For instance, let’s say you need to calculate age in Excel as of June 15th, 2024, for someone born on August 15th, 2003. If you merely subtract 2003 from 2024, you’ll get 21 years. But this calculation doesn’t account for the fact that the person was born in August, meaning they haven’t yet completed their 21st year.
To address these nuances, Excel offers powerful formulas that can calculate age with precision, taking into account years, months, and even days.
In this section, we’ll explore these formulas in detail, starting with counting the number of years, and then moving on to finding the age in terms of days, months, and years combined.
Download Sample Data
To ensure you can follow along seamlessly with the examples in this tutorial on how to calculate age in Excel, we recommend downloading the sample data used throughout the article. You can do that using the link below
Related Article: How to Calculate Age in Google Sheets
Calculating Age in Years Using Excel Formulas
Having understood the basics of what it entails to calculate age in Excel, we want to take things up a notch by exploring how to calculate age in years using Excel formulas.
To illustrate the process, we are going to be using the following sample data where we have the date of birth in cell B2 and the reference date (such as today’s date or any other date you want to calculate the age from) in cell B3.
In this scenario, you can leverage two powerful formulas in Excel to calculate age in years: DATEDIF and YEARFRAC.
The DATEDIF function calculates the difference between two dates based on the specified date part (years, months, days, etc.). Meanwhile, the YEARFRAC function returns the fraction of the year represented by the number of whole days between two dates.
In the upcoming sections, we’ll dive into the specifics of using these formulas to calculate age in Excel, ensuring you can accurately determine the number of years that have elapsed between any two given dates.
Using the DATEDIF Formula to Calculate Age In Years
The DATEDIF function stands out as an exceptional resource, effortlessly facilitating age calculation in years.
But rather than just take our word for it, allow us to demonstrate how you can leverage this tool to accurately determine age in years, using our sample data as a guide.
Step 1: Select an Empty Cell
Begin by selecting an empty cell within your spreadsheet to display the calculated age in years. For this example, we’ll use cell B6 as our designated location.
Step 2: Enter the DATEDIF Formula
Now that you’ve chosen an area in your spreadsheet for the age calculation result, it’s time to input the DATEDIF formula.
To accomplish this, go to the formula bar and enter the following formula:
=DATEDIF(B2,B3,”Y”)
The formula above accepts three parameters:
- Start Date: For our case, this represents the individual’s date of birth.
- End Date: In this scenario, it refers to today’s date.
- Unit: This parameter instructs the DATEDIF function on the specific calculation to perform. In our illustration, we’ve selected “Y” to indicate that the function should compute the total years elapsed between the two dates provided.
Step 3: Press Enter
Once you’ve inputted the formula outlined in Step 2, press the Enter key. Excel will immediately calculate and display the age in years within the chosen cell.
Here is what it looks like:
From the screenshot above, you can see that we have been able to use the DATEDIF function to find the correct age in years almost seamlessly.
Note: If your Excel spreadsheet is returning unexpected results and you believe your formula is accurate, the issue may lie with the cell formatting. To correct this, right-click the cell displaying the result, select “Format Cells” from the context menu, and then choose “General.” This adjustment should ensure that the cell displays the correct output.
Understanding DATEDIF Function Units: Calculating Age in Years, Months, and Days
From the example we just wrapped up, you’ll notice that we used “Y” for the dated function unit. But guess what? That isn’t the only unit you can use when trying to calculate age in Excel using the DATEDIF function.
In this section, we will explore the various units you can specify for the third parameter in the DATEDIF function.
These units are critical when you don’t only want to calculate the years but also months and days too.
Here are some critical unit options for the DATEDIF function and their respective uses:
- “Y” – Calculates the total elapsed years between two specified dates.
- “M” – Determines the total elapsed months between two specified dates.
- “D” – Computes the total number of days between two specified dates.
- “MD” – Gives the difference in days between two dates, excluding entire months and years that have elapsed.
- “YM” – Offers the difference in months between two dates, disregarding completed years.
- “YD” – Provides the difference in days between two dates, not accounting for full years that have passed.
We understand that this can be a bit confusing right now. But not to worry. You’ll get the hang of it when we begin to use some of these units in subsequent examples.
Using the YEARFRAC Formula to Calculate Age in Excel
In the previous example, we explored the DATEDIF function and how it can be used to calculate age in years by specifying the “Y” unit. However, Excel offers another useful formula for this purpose: YEARFRAC.
The YEARFRAC function returns the fraction of the year represented by the number of whole days between two dates.
This can be particularly helpful when calculating age with a higher degree of precision, accounting for the exact number of days between the date of birth and the reference date.
In this section, we will show you how to use the YEARFRAC formula to calculate age in years in Excel.
We will use the same sample data we used for the first example to demonstrate the step-by-step process.
Step 1: Choose a Blank Cell
Like in the first example, we need to choose an area in our spreadsheet where we want the result to be. For this particular scenario, let’s go with cell B6.
Step 2: Input the YEARFRAC formula
After choosing the cell where you want your result replicated, navigate to the formula bar and type the following formula:
=INT(YEARFRAC(B2,TODAY()))
Step 3: Press Enter
Having executed step 2 as we showed you, go ahead and press the Enter button on your keyboard; you should get the generated result in the selected cell.
Here is what ours looks like:
The YEARFRAC function is designed to calculate the duration between two specific dates in terms of years, including fractions of years. To tailor this output to only show the age in whole years, we included the INT function, ensuring that only the integer portion of the age is returned.
Bonus Tips: To enhance accuracy when dealing with dates in Excel, employing the DATE function for entering specific dates into cells is highly recommended. This method reduces the likelihood of encountering errors, such as inputting a date format that Excel fails to recognise as a valid date.
Calculating Age in Years, Months, and Days
In the previous examples, we covered how to calculate age in years using the DATEDIF and YEARFRAC functions in Excel.
While these formulas provide the age in terms of complete years, there may be situations where you need a more detailed breakdown that includes months and days as well.
To get the age in years, months, and days, we can leverage the versatility of the DATEDIF function by using different units for each component of the age calculation.
Let’s use the DATEDIF function in Excel to calculate age in years, months, and days.
We will also use the same sample data as the previous examples to demonstrate the entire process.
Step 1: Choose an Empty Cell
Let’s begin by choosing an empty cell where we want the result for the total number of months that have passed. For our example, we will go ahead and select cell C7.
Step 2: Input the DATEDIF function
Now, let’s input our DATEDIF formula in the selected cell. What you want to do is head to the formula bar and type in the following formula:
=DATEDIF(B2,B3,”YM”)
Step 3: Hit Enter
To see the result, press the Enter button on your keyboard. Excel will generate the result almost instantly in the selected cell.
Here is what ours looks like:
From the screenshot above, you can see that we have been able to get the result for the total number of months passed.
What this means is that 9 months have passed after 20 years completed.
Now, let’s show you how to find the total number of days passed. We will include this as step 4.
Step 4: Calculate the Number of Days Passed
To calculate the number of days passed, choose an empty cell where you want the result to be. In our sample sheet, we already have a space for that, which is cell C8.
Having selected where you want the result for days passed generated, navigate to the formula bar and type in the following formula:
=DATEDIF(B2,B3,”MD”)
With the formula entered correctly, go ahead and press the Enter button; you should see the result instantly generated in the selected cell.
For our sample data, we got 0.
Note: Since we are calculating the days after accounting for the months, and the two dates (15/09/2003 and 15/06/2024) fall on the same day of the month, there are zero days left over after counting complete months. That’s why we got zero.
In situations where you don’t want to execute these steps one by one and you just want to calculate age in years, months, and days in one cell, you can use the following formula:
=DATEDIF(B2,B3,”Y”)&” Years “&DATEDIF(B2,B3,”YM”)&” Months “&DATEDIF(B2,B3,”MD”)&” Days“
So, let’s input this with our sample data to show you the result.
First, choose a new cell in your spreadsheet and name it accordingly. For our example, we will select cell A10 and name it: Age in Years, Months, and Days.
Having completed that, let’s choose where we want the result to be. For this scenario, we will go with cell B10.
Now that you know exactly where you want the result for age in years, months and days to be, head to the formula bar and type in the following formula.
=DATEDIF(B2,B3,”Y”)&” Years “&DATEDIF(B2,B3,”YM”)&” Months “&DATEDIF(B2,B3,”MD”)&” Days”
To generate the result, simply press the Enter button. You should get the result for age in years, months, and days in the selected cell.
Here is what ours looks like:
From the screenshot above, you can see that we have successfully calculated the age in years, months, and days in one cell using a simple formula. This saves us the hassle of repeating steps, one after the other.
Final Thoughts
Knowing how to calculate age in Excel is a valuable skill that can streamline various data analysis tasks across different domains. Throughout this article, we’ve explored multiple formulas and techniques to calculate age in Excel, catering to diverse requirements.
The DATEDIF function emerged as a powerful tool, enabling us to calculate age in years, months, or days by specifying the appropriate unit parameter. By combining different units, you can obtain a comprehensive breakdown of age in years, months, and days simultaneously.
Alternatively, the YEARFRAC function proved to be an excellent choice for calculating age in Excel when you need to account for fractions of years, providing a more precise age calculation based on the number of days between two dates.
Whether you’re managing employee records, analyzing customer data, or working with any other date-based information, mastering these age calculation formulas in Excel will undoubtedly enhance your data analysis capabilities.
Remember, the key to accurate age calculations in Excel lies in understanding the nuances of date formats, cell formatting, and the specific requirements of your data set.
By combining the formulas and techniques outlined in this article with careful attention to detail, you can confidently calculate age in Excel for any scenario.