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.

**Table of Contents**hide

## 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**.

`=(TODAY()-C5)/365.25`

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**.

`=INT(D5)`

*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**.

`=IF(MONTH(NOW())>MONTH(C5),YEAR(NOW())-YEAR(C5)&"yr "&MONTH(NOW())-MONTH(C5)&"mo",YEAR(NOW())-(YEAR(C5)+1)&"yr "&(MONTH(NOW())+12)-MONTH(C5)&"mo")`

**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:

`YEAR(NOW())-YEAR(C5)&"yr "&MONTH(NOW())-MONTH(C5)&"mo"`

Otherwise, the rest of the formula will work:

`YEAR(NOW())-(YEAR(C5)+1)&"yr "&(MONTH(NOW())+12)-MONTH(C5)&"mo"`

*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:

`=DATE(C5,MONTH(DATEVALUE(D5&"1")),E5)`

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

- Select cell
**G5**and paste down the formula below.

`=DATEDIF(DATE(C5, MONTH(DATEVALUE(D5&"1")), E5), TODAY(), "y") & " Years, "& DATEDIF(DATE(C5, MONTH(DATEVALUE(D5&"1")), E5),TODAY(), "ym") & " Months, "& DATEDIF(DATE(C5, MONTH(DATEVALUE(D5&"1")), E5), TODAY(), "md") & " Days"`

**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.

`=YEARFRAC(C5,TODAY(),1)`

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**.

`=DATEDIF(C5,TODAY(),"Y") & " Years, " & DATEDIF(C5,TODAY(),"YM") & " Months, " & DATEDIF(C5,TODAY(),"MD") & " Days"`

**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**.

`=IF(DATEDIF(C5, TODAY(),"y")=0,"",DATEDIF(C5, TODAY(),"y")&" years, ")& IF(DATEDIF(C5, TODAY(),"ym")=0,"",DATEDIF(C5, TODAY(),"ym")&" months, ")& IF(DATEDIF(C5, TODAY(),"md")=0,"",DATEDIF(C5, TODAY(),"md")&" days")`

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**.

`=DATEDIF(C5, D5,"Y") & " Years, "& DATEDIF(C5,D5,"YM") & " Months, "&DATEDIF(C5,D5, "MD") & " Days"`

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**.

`=DATE(YEAR(C5)+50,MONTH(C5),DAY(C5))`

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.