Want to Calculate Age on a Specific Date using Excel formula but don’t know how? We are here for you. We have accumulated 8 different methods for you to choose from in calculating age. Hope you like them.

## 📁 Download Excel File

You can download this workbook to practice for yourself.

## Calculate Age on a Specific Date Using Excel Formula with These 8 Methods

Today we have discussed 8 simple and innovative methods to Calculate Age on a Specific Date using an Excel formula. We will use a dataset of different girls with their birthdates and we will calculate age on the current date and also on any given date. Hope you will enjoy them.

**Method 1**

### 1. Applying TODAY and INT Functions

Let’s start with a simple formula. If we want to calculate someone’s age from the current date, we usually subtract the birth year from the present year. That is what we are going to do in this method by using the **TODAY **function.

Let’s see how to use the **TODAY** formula to calculate age from the current date in Excel. In our example, we have **C** column where there are some Birth dates. We want to show these girls’ age in years in column **D**.

**⬇️⬇️ STEPS ⬇️⬇️**

- First, Select Cell
**C6**. - Next, we go to the
**Formula bar**where we type the following formula:

`=(TODAY()-C6)/365`

- Then, when you hit
**Enter**, you will see the age in fractions. We will use the Fill**Handle**to do the same for every Row.

If you don’t want your age to be in fractions we can remedy that too. We will use **the INT Function**. This function takes any number as argument and returns the closest integer to the argument.

**⬇️⬇️ STEPS ⬇️⬇️**

- Select, Cell
**E6**. - Then, we go to Formula Bar where we type this formula:

`=INT((TODAY()-C6)/365)`

- At last, we hit
**Enter**and you will see the age in integer format. We use**Fill Handle**to copy the formula in every Row.

In the first formula,**(TODAY()-C6)** calculates the difference between the present date and date of birth in days which is why we divided the result by 365 to get the Years. To include leap years in your results divide by 365.25. As you can see it returns our age in fractions so we used the INT function before the original formula in the second one to get our age in integer format.

**Method 2**

We can also use another simple Function which is the **YEAR**(your data cell reference) Function. Using the **YEAR** Function, we will be able to calculate age by subtracting the date of birth from the current year. Along with that, we will also use **the NOW function** for our calculations. This function returns the current date and time. Follow the following steps to see how.

**⬇️⬇️ STEPS ⬇️⬇️**

- First, select the cell where you want to see your age. In the following example, we selected
**E6**. - Now, type the following formula in the
**Formula Bar**.

`=(YEAR(NOW())-YEAR(C6))`

- Then we hit
**Enter**to see our age. To show everyone’s age, use the**Fill Handle**to copy the formula in every Row.

We can also calculate age on a specific date other than the current date. Let’s do that step by step.

**⬇️⬇️ STEPS ⬇️⬇️**

- First, select the cell no you want your result to be in. We selected
**F6**. - After that, we use the following formula in the
**Formula Bar**. In cell**D5**, we have our specific dates and in cell**C6**we have our birth dates.

`=YEAR(D5)-YEAR(C6)`

- Finally, we hit
**Enter**to get our age. Like always, use the**Fill Handle**to copy the formula till**F12**.

**Method 3**

📕** Read More: How to Calculate Average Tenure of Employees in Excel**

### 3. Combining YEARFRAC and ROUNDDOWN Functions

Another prudent way to Calculate age on a specific date in Excel is **the YEARFRAC function**. But before using that, let’s know about this function in depth.

The format of the function is as follows:

`YEARFRAC(start_date, end_date, [basis])`

Here,

**Start date**– Date of Birth

**End date**– The Date you want to calculate your age on

**Basis**– Basis has a couple of numbers to represent different things. In this example,we will use 1 which dictates Excel to divide the actual number of days per month by the actual number of days per year.

Let’s dive into Excel to see how it works.

**⬇️⬇️ STEPS ⬇️⬇️**

- We’ll select
**E6**in our example. - Then, we go to the
**Formula Bar**where we type**=YEARFRAC(**. We select the cell where our Date of Birth is,**C6**. Next type comma(,) and**Today()**and again comma(,). - Finally, we give basis number 1 and close the bracket.

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

- Hit
**Enter**to see the result. Use the**Fill Handle**to do the same thing for every row.

Here, you can see we have found a fraction result. We can avoid that by using the **ROUNDDOWN** function as shown here.

**⬇️⬇️ STEPS ⬇️⬇️**

- Select
- Now,in the
**Formula Bar**,we type**=ROUNDDOWN(YEARFRAC(**and then select our cells where we have our Date of Birth,type**TODAY()**and give a Basis number.Then we close bracket for the**YEARFRAC**function. - Before closing the bracket for the
**ROUNDDOWN**function we will give 0 so that the function doesn’t return any decimal places. Follow the below formula if you have any confusion.

`=ROUNDDOWN(YEARFRAC(C6,TODAY(),1),0)`

- Hit
**Enter**to see your desired result. As always, use**Fill Handle**to cover every Row.

We can also Calculate Age on a specific date using this Excel formula. Just use the cell reference where your date is on the ‘end date’ section instead of using **Today()**. We have done that in our example.

`=ROUNDDOWN(YEARFRAC(C6,D6,1),0)`

Apply **Fill Handle** to copy the formula to the entire Column.

**Method 4**

One of the convenient ways of calculating age on a specific date in Excel is with **the DATEDIF function**. Let’s discuss this function first.

Format of the Function:

`DATEDIF(start_date, end_date, unit)`

This function provides the age difference on a specific date with years, months, and days. It depends on the Unit you supply in the unit argument. The arguments are as follows.

- Y – returns the age in years between the start and end dates.
- M – returns the age in months between the dates.
- D – returns the age in days between the two dates.
- YM – returns months of the last year without days and years.
- MD – returns the days of the last month without months and years.
- YD – returns the difference in days between the start and end dates, without calculating years.

We are going to show you how you can find out age in years, and months and also we will find the last months in the last provided dates. As you can see in our example we have three different columns for finding Years, Months, and Last months.

**⬇️⬇️ STEPS ⬇️⬇️**

- First, Select
**E6**and go to**Formula Bar**. - Second, Type in the following formula. We selected
**C6**as it contains the Date of Birth and as the end date, we selected a specific date which is in**D5**.

`=DATEDIF(C6,D6,"y")`

- We also put “Y” as the unit to tell Excel to give us Years between our dates.

- Hit
**Enter**and you will get your result. Drag your mouse to the end of the column using**Fill Handle**to copy the formula.

- You can enter “M” to get the total months.

- Repeat
**Enter**and**Fill Handle**combination.

- You can also enter “YM” to get the last months as a unit.

- Again, Press
**Enter**and use the**Fill Handle**combination.

**Method 5**

### 5. Utilizing DATE Function with DATEDIF

When your date is split among 3 different cells, you can use **the DATE function** in conjunction with **DATEDIF**. But we have to let Excel find those values as Years, Months, and days so that with the **DATE** function it can formulate the Date. Like this,

`DATE(year,month,day)`

In the year we have to input the cell no, where the year resides and likewise in month and day we will provide the cell references to where months and days’ data are located. Let’s calculate age using **DATEDIF** and **DATE** functions.

**⬇️⬇️ STEPS ⬇️⬇️**

- First, select
**G5**and place your mouse in**Formula Bar**. - Then type the following formula.

`=DATEDIF(DATE(C7,D7,E7),F7,"y")`

- Press the
**Enter**key and you will get your result. Use**Fill Handle**to copy the formula to the entire column.

- To get Years, Months, and Days all at once, just type the following formula.

`=DATEDIF(DATE(C7,D7,E7),F7,"Y") &" Years " & DATEDIF(DATE(C7,D7,E7),F7,"YM") & " Months " & DATEDIF(DATE(C7,D7,E7),F7,"MD") & " Days"`

- Finally, Press
**Enter**and use**Fill Handle**to drag the formula to the entire column.

As you can see we used the **DATEDIF** function three times to get Years, Months, and Dates. You can also notice that after each time we provided “ years”,” months” and “ days” text strings to show the results.

**Method 6**

**📕 Read More: 5 Ways to Use Formula to Find Next Month in Excel**

### 6. Working with DATEDIF and CONCATENATE Functions

If your dates are in one cell, you can use **CONCATENATE** with **DATEDIF** functions to calculate age on a specific date. Let’s see how it’s done.

**⬇️⬇️ STEPS ⬇️⬇️**

- First, Select Cell
**E6**, and in the**Formula Bar**, enter the following formula.

`=CONCATENATE(DATEDIF(C6,D6,"Y")," Years ",DATEDIF(C6,D6,"YM")," Months ",DATEDIF(C6,D6,"MD")," Days")`

- Hit
**Enter**and use**Fill Handle**to copy the formula on the entire column.

**Method 7**

### 7. Applying IF with DATEDIF Functions

You may have noticed that In Methods 5 and 6, in a couple of Rows, we found 0 months or 0 days. Now we can command Excel to hide Zero Values. To do that, we will have to add **IF** statements to each of the **DATEDIF** functions.IF returns only non-zero values. Let’s dive into the steps.

**⬇️⬇️ STEPS ⬇️⬇️**

- As usual, Selecting our Display cell
**E6**, we put our mouse cursor in the**Formula Bar**. - Now, copy or type the following formula which will eliminate non-zero values.

`=IF(DATEDIF(C6,D6,"y")=0,"",DATEDIF(C6,D6,"Y")&" Years ")& IF(DATEDIF(C6,D6,"ym")=0,"",DATEDIF(C6,D6,"YM")&" Months ")& IF(DATEDIF(C6,D6,"md")=0,"",DATEDIF(C6,D6,"MD")&" days")`

- Press
**Enter**and use**Fill Handle**to copy the formula on the entire column. You’ll notice all the Zeros have been omitted in the results.

So how does **IF** work, you may wonder. To understand that Let’s see the format of the **IF** function.

**IF(logical test, [value_if_true], [value_if_false])**

The **IF** function determines whether a condition is true or false and returns one value if the condition is true and returns another if it is false. So in the formula,

Logical Test- we give the conditions we want Excel to check.

Value if true- What we want Excel to print if our given condition is met

Value if false- What we want Excela to print if our given condition is not met

Now if look at the formula we wrote for our example, you will understand that we wanted to avoid any non-zero values. So We give a condition like this: **DATEDIF(C6,D5,”y”)=0**, meaning ‘if Excel finds any Zero value’, in the **value_if_true** section we kept it blank which will tell Excel not to print anything and finally in the **value_if_false** section we gave the formula to print our non-zero results.

**Method 8**

### 8. Using VBA

Finally, we have come to our last method where we will be using the VBA script to calculate age on a specific date in Excel. To write VBA code we need to enable the **Developer **option in our Ribbon. Find here how to **access the Developer tab**.

After you enable the Developer tab, follow these steps to write your code.

**⬇️⬇️ STEPS ⬇️⬇️**

- Select
**the Developer**Tab and click**Visual Basic**at the left corner of the Ribbon.

- A window(
**Microsoft Visual Basic for Applications****)**will appear where we will write our code to calculate age all at once. Go to**Insert**and click**Module**.

- A white module will show up to write your code. I have already included the code so that you may copy and paste it into your module.

```
Sub agecalculation()
Dim tillend As Long
With ThisWorkbook.Worksheets("VBA")
tillend = .Cells(Rows.Count, "C").End(xlUp).Row
.Range("E6:E" & tillend) = "=YEAR(TODAY())-YEAR(C6)" .Range("F6:F" & tillend) = "=YEAR(D6)-YEAR(C6)"
End With
End Sub
```

- Finally, press
**F5**or click on**Run**and click**Run Sub/Userform**.

You will see all of your results in the Excel workbook.

In the **Rows. Count**, we gave column **C** because that’s where our dates of birth are and in the **Range** property we are telling Excel Three things. First, which Row to start from, second, which Column to print our results, and finally to calculate as long as it gets the Date of Birth. After that, we wrote our formula to calculate age.

**📕 Read More: 5 Quick Ways to Use Excel Formula to Count Days from Date**

## 📄 Important Notes

`🖊️`

Excel date functions will only work from dates 1/1/1900.

`🖊️`

Before 1900, any date you provide will be stored as text.

## 📝 Takeaway from This Article

`📌`

Readers will learn the use of different functions which are explained in simple language.

`📌`

Readers will be able to use these functions in other works as well.

## Conclusion

We have come to the conclusion of our article.I hope with these Excel functions and formulas you will be able to calculate age on a specific date with ease. If you have any questions, feel free to comment below. I will try to answer them as soon as possible. Have fun using Excel and you can visit **Excelden.com** for more Tutorials. Thank you.