Excel can be handy while calculating age, you may wonder how you can do that easily. In this article, I will show you how to **calculate age on a specific date** with a formula in Excel. For conducting the session, I used **Office 365**. You can use any version.

**Table of Contents**hide

## Basics of Date Related Functions

It will be fun for you when you get used to several Excel functions. Same as date or time-related functions.

- There is a function called
**the TODAY function**. This function returns the current date If you just write**TODAY()**and then hit**Enter**you will get the date of the current day. - Another function I prefer mentioning here is
**the NOW function**. This function returns the current date and time with formatting. If you write**NOW()**and then press**Enter**then the function will return the current date and time.

Before diving into the bigger picture, let’s get to know about today’s practice workbook. There are **3** columns, **Name**, **Date of Birth**, and **Specific Date**. Using the data in this table we will **calculate** **the age on a specific date**. I will show you how to **calculate age on a specific date** using Excel formula in 5 different ways.

## 1. Using Excel Formula with INT Function to Calculate Age on Specific Date

To calculate age you simply need to get the difference between two dates **(Date of Birth and Calculating Date)**. Since age is calculated on a yearly basis, you will need to divide the difference by **365**.

So, the formula will be like this,

**(Calculating Date – Date of Birth) / 365**

But this may give us value in fractions. We don’t want age as a fraction value (unless any rear case). So while writing in Excel we will calculate this within **the INT function**. So the formula will be,

**INT((Calculating Date – Date of Birth) / 365))**

Let’s see how to **calculate age on a specific date **using the generic formula in Excel.

**Steps:**

- In the beginning, select the cell where you want to calculate the
**Age**. here, I selected cell**E5**. - Then, in cell
**E5**write the following formula.

`=INT((D5-C5)/365)`

- After that, press
**Enter**to get the**Age**.

**🔎** **How Does the Formula Work?**

**(D5-C5)/365:**Here, the value in cell**C5**is**subtracted**from the value in cell**D5**. And then, the result is**divid**ed by**365.****INT((D5-C5)/365):**Now, the**INT**function returns the integer portion of the decimal number.

- Next, drag the
**Fill Handle**down to copy the formula.

- Finally, you can see that I have copied the formula to the other cells.

**Read More: How to Calculate Age Between Two Dates in Excel**

## 2. Applying IF Formula to Calculate Age on Specific Date in Excel

If there is a situation where anyone inserts a date older than the **Date of Birth**, what will happen then? To nullify that kind of situation you can use **the IF function**. Let’s see the steps.

**Steps:**

- Firstly, select the cell where you want to calculate
**Age**. - Secondly, write the following formula in that selected cell.

`=IF(D5>C5,INT((D5-C5)/365),"Not Born")`

- Thirdly, press
**Enter**and you will get the**Age**.

**🔎** **How Does the Formula Work?**

**(D5-C5)/365:**Here, the value in cell**C5**is**subtracted**from the value in cell**D5**. And then, the result is**divided**by**365**.**INT((D5-C5)/365):**Now, the**INT**function returns the integer portion of the decimal number.**IF(D5>C5,INT((D5-C5)/365),”Not Born”):**The**IF**function checks if the value in cell**D5**is**greater than**the value in cell**C5**. If the**logical_test**is**True**then the formula will return the**Age**. Otherwise, It will return**“Not Born”**.

- After that, drag the
**Fill Handle**down to copy the formula to the other cells.

- Lastly, in the following picture, you can see that I have copied the formula to all the other cells and got my desired output.

## 3. Calculating Age on Specific Date with Excel YEARFRAC Function

You can calculate age using **the YEARFRAC function**. This function takes** three** parameters, **start_date**, **end_date,** and **basis**. The** basis** is the day count base. Like if you use** 0** it will count setting the base **360 **days in a year. If you use **3**, it will count on the base of **365** days in a year. Here, I will use** 3**.

Writing this function may give a fraction value. So, I will round this using** the ROUNDDOWN function**. This function takes **two **parameters. **First**, the value you want to round, and the **second** one is how many decimal points you want. Since I don’t want to see any decimal in the Age value, I will use **0**.

Let’s see how you can use the** YEARFRAC** function and the **ROUNDDOWN** function in Excel formula to **calculate age on a specific date**.

**Steps:**

- First, select the cell where you want to calculate the
**Age**. Here, I selected cell**E5**. - Next, in cell
**E5**write the following formula.

`=ROUNDDOWN(YEARFRAC(C5,D5,3),0)`

- Afterward, press
**Enter**.

**🔎** **How Does the Formula Work?**

**YEARFRAC(C5,D5,3):**Here, the**YEARFRAC**function returns a decimal value that represents the years between the date in cells**C5**and**D5**.**ROUNDDOWN(YEARFRAC(C5,D5,3),0):**Now, the**ROUNDDOWN**function round downs the decimal value to zero decimal point or you can say integer.

- Then, drag the
**Fill Handle**down to copy the formula to the other cells.

- Consequently, you can see that I have copied the formula to the other cells.

**Similar Readings**

**How to Calculate Age in Excel in dd/mm/yyyy****How to Convert Date of Birth to Age in Excel****Excel VBA: Calculate Age from Date of Birth**

## 4. Applying Excel DATEDIF Formula to Get Age on Specific Date

You can get the age by using **the DATEDIF function**. This function has **three** parameters, **start_date**, **end_date**, and **format**. Let’s see how you can employ the **DATEDIF** function in the Excel formula to **calculate the age on a specific date**.

**Steps:**

- Firstly, select the cell where you want the
**Age**. Here, I selected cell**E5**. - Secondly, in cell
**E5**write the following formula.

`=DATEDIF(C5,D5,"Y")`

- Thirdly, press
**Enter**to get the result.

**DATEDIF**function, I selected cell

**C5**as

**start_date**,

**D5**as

**end_date**, and

**“Y”**as a

**unit**.

**“Y”**means the complete years during this period. The formula returns the years between these two dates.

- After that, drag the
**Fill Handle**to copy the formula to the other cells.

- Finally, you can see that I have copied the formula to the other cells.

## 5. Determining Age with Years, Months, and Days in Excel

Now, in some situations, you may need to present the **age** with **years**, **months**, and **days**. Let’s see how you can do that. I will use the **DATEDIF** function and the **Ampersand Operator (&) **for this calculation. The **Ampersand Operator (&)** is used for joining more than one function together. In the beginning, I will calculate the difference in year format, then in months, and then in days.

Let’s see how you can **calculate age with years, months, and days on a specific date** by using an Excel formula.

**Steps:**

- In the beginning, select the cell where you want to calculate the
**Age**. - Then, write the following formula in that selected cell.

`=DATEDIF(C5,D5,"Y")&"Y " & DATEDIF(C5,D5,"YM")&"M " &DATEDIF(C5,D5,"MD")&"D"`

- Next, press
**Enter**to get the**Age**.

**🔎** **How Does the Formula Work?**

**DATEDIF(C5,D5,”Y”):**Here, the**DATEDIF**function returns the years between the dates in cells**C5**and**D5**.**DATEDIF(C5,D5,”YM”):**Now, the**DATEDIF**function returns the number of months between the dates in cells**C5**and**D5**ignoring the days and years.**DATEDIF(C5,D5,”MD”):**Here, the**DATEDIF**function returns the number of days between the dates in cells**C5**and**D5**ignoring the months and years.**DATEDIF(C5,D5,”Y”)&”Y ” & DATEDIF(C5,D5,”YM”)&”M ” &DATEDIF(C5,D5,”MD”)&”D”:**Now, the**Ampersand Operator (&)**joins the formulas.

- Afterward, drag the
**Fill Handle**down to copy the formula to the other cells.

- In the end, you can see that I have copied the formula and got my desired output.

**Read More: How to Calculate Age in Excel in Years and Months**

## 6. Calculating Age from Date of Birth in Excel

In this section, I will show you how you can **calculate the age** when only the **Date of Birth is given**. I will use the **DATEDIF** function and the **TODAY** function for this calculation. Let’s see the steps.

**Steps:**

- Firstly, select the cell where you want to calculate the
**Age**. Here, I selected cell**D5**. - Secondly, in cell
**D5**write the following formula.

`=DATEDIF(C5,TODAY(),"Y")`

- After that, press
**Enter**to get the**Age**.

**🔎** **How Does the Formula Work?**

**TODAY():**Here, the**TODAY**function returns the**current date**.**DATEDIF(C5,TODAY(),”Y”):**Now, the**DATEDIF**function returns the years between the date in cell**C5**and the**current date**.

- Then, drag the
**Fill Handle**to copy the formula.

- Finally, you can see that I have copied the formula to the other cells and got
**Age**from the**Date of Birth**.

**Download Practice Workbook**

Here, I have shared the practice workbook. You can download it from the link below.

## Conclusion

That’s all for today. I’ve tried listing a couple of ways to **calculate age on a specific date **with an Excel formula. Hope that will help you. Feel free to comment if something seems hard to understand. Let us know which of the methods you are going to use. You can write your own way of calculating age.

## Related Articles

**How to Calculate Current Age in Excel****How to Calculate Age in Excel for Entire Column****How to Calculate Age in Excel from ID Number****How to Calculate Retirement Age in Excel****Metabolic Age Calculator in Excel****How to Calculate Average Age in Excel****How to Group Age Range in Excel with VLOOKUP****How to Make Age Pyramid in Excel****How to Create Age and Gender Chart in Excel****How to Create Age Distribution Graph in Excel**