How to Calculate Age from Birthday in Excel (8 Easy Methods)

The article demonstrates some amazing methods to calculate age from a birthday in Excel. You’ll learn a few formulas for calculating age as a number of complete years, as well as getting actual ages in years, months, and days at today’s date or a certain date. Now, we will take you through those easy and convenient methods on how to calculate age from birthday in Excel.

Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.

8 Methods to Calculate Age from Birthday in Excel

“Hey, how old are you? “ A very commonly asked question. From our childhood, we all must have heard this question frequently. But, what does that mean? It usually denotes a response expressing how long you’ve been alive.

Although there is no dedicated function to calculate age from birthday in Excel, there are a few alternative ways to convert the date of birth to age.

We have a list of Birthdays of 10 individuals.

At this moment, we’ll calculate their ages in Excel from the birthdays.

1. Using Basic Formula to Calculate Age from Birthday in Excel

At first, we’ll learn the most traditional way to calculate age in years in Excel. What is the most common method for determining someone’s age? Simply subtract the birth date from the current date. This common formula for calculating age can also be utilized in Excel. Follow the steps carefully.

Steps:

• First, select cell D5, enter the formula below, and press ENTER.

Here, the C5 is the starting cell of the Birthday column.

The TODAY function returns today’s date. Naturally, 1 year consists of 365 days. But leap year comes every 4 years, so we divide the date differences by 365.25. Use the Fill Handle Tool and drag it down to complete column D.

• Then, if you wanna display the results in just complete years, use the INT function.

Note: Ensure that cells D5:D14 are formatted in Number or General.

Read More: How to Calculate Average Age in Excel (7 Easy Methods)

2. Applying Formula Combination of IF, YEAR, MONTH, and NOW Function

This method applies a formula combining some distinct functions. IF, YEAR, MONTH, and NOW functions have their own operations here. Please follow the steps below.

Steps:

• Firstly, select cell D5 and write down the formula below, and tap ENTER.

Formula Breakdown

Here, MONTH and YEAR functions return the month and year of that date as a number. We used the IF function to insert a logical test that, if the month of the present date is greater than the month of birthdate, then the formula works is:

Otherwise, the rest of the formula will work:

Note: The drawback of this method is that it returns 12 months instead of adding 1 year. We can see that in Jones’s case.

3. Calculating Age from Birth Year, Month, and Day in Different Cells

Here, we got our birthdate in different cells by splitting them into Year, Month, and Day.

From this table, we wanna calculate their present age. Follow our work steps carefully.

Steps:

• Firstly, we’ve to get the date of birth using the DATE and DATEVALUE functions. To do so, we used this formula:

This formula merged the different years, months, and days into one cell.

• Select cell G5 and paste down the formula below.

Read More: How to Calculate Age in Excel in Years and Months (5 Easy Ways)

4. Implementing YEARFRAC Function

Using the YEARFRAC function, which returns a fraction of the year, is also a reliable approach to calculating age from birthdays in Excel. Follow the steps below.

Steps:

• Select cell D5 and type down the formula below.

This function calculated the difference between birthday and today’s date. Here we take the basis as 1 which means actual.

• For showing the age in complete years, use the INT function.

5. Adopting DATEDIF Function to Calculate Age from Birthday in Excel

The most convenient and usable function for calculating the age is the DATEDIF function. Follow the steps thoroughly.

Steps:

• In the beginning, select cell D5 and paste down the formula below, and press ENTER.

Formula Breakdown

To get the age in years, months, and days we’ve used the arguments “Y”, “YM”, and “MD” sequentially in the DATEDIF function. The formula above returns a single text string with 3 values (years, months, and days) concatenated.

6. Combining IF with DATEDIF Function to Display Only Non-Zero Values

In our previous method, you may notice a problem that there are 0 months and 0 days showing in some cells.

To avoid this problem and display just non-zero values, we could do some renovation to our previous formula by combining the IF function. To get updated with the new formula, observe carefully.

Steps:

• First, select cell D5 and write down the formula as follows, and press ENTER.

This formula is just like our previous formula but the only difference is that if there is any zero value in years, months, or days, those terms will be omitted. Using the IF function we applied this condition to display just non-zero values.

7. Calculating Age at a Specific Date

In our previous methods, we learned how to calculate the present age from the birthday. Now, we’ll know the procedure of determining the age on a specific date.

For foretelling age at a specific date, we used the DATEDIF function again. From this, you obviously can understand the convenience of using this function. Follow the steps below.

Steps:

• Select cell E5 and type down the formula below and press ENTER.

The difference from our previous methods is that there we calculated the age till the present date. Thus, we used the TODAY function. But here we work out the age on a specific date, not the present age. So we use another cell reference for the End Date.

Read More: How to Calculate Age Between Two Dates in Excel (6 Useful Methods)

8. Applying VBA to Calculate Age from Birthday in Excel

Another way to calculate age in Excel from birthdays is to use the VBA code. You can follow the steps below to calculate the age using this method.

Steps:

• Right-click on the Sheet name and select View Code.

• Instantly, the Microsoft Visual Basic for Applications window opens. Right-click on Sheet9 (VBA) > select Insert > Module.

• It opens a code module, where paste the below code down and click on the Run button or press F5.

Sub Age()
Dim LastRow As Long
With ThisWorkbook.Worksheets("VBA")
LastRow = .Cells(Rows.Count, "C").End(xlUp).Row
.Range("D5:D" & LastRow) = "=DATEDIF(C5,Now(),""y"")"
End With
End Sub

• Now closing the code module return back to the worksheet. You can see that D5:D14 cells have been automatically filled with the age in years. In the formula bar, we can see the DATEDIF function which we’ve used in our VBA code.

Finding out the Date When a Person Reaches a Given Age

Let’s say, Robin’s date of birth is 13-08-1996. When would he attain his 50 years of age? How do you know that? Nothing to worry about if you didn’t know it earlier. Just watch our steps carefully.

Steps:

• Firstly, select cell D5 and write down the formula as follows, then press ENTER.

Here, we’ve just added 50 with the year of the birth date. Eventually, it returns on the Date of Attaining 50 Years Age.

Conclusion

Thank you for reading this article, we hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website Exceldemy to explore more.

Related Articles

• How to Calculate Age in Excel from ID Number (4 Quick Methods)
• Calculate Retirement Age in Excel (4 Quick Methods)
• How to Group Age Range in Excel with VLOOKUP (With Quick Steps)
• How to Calculate Age in Excel in dd/mm/yyyy (2 Easy Ways)