Excel can be handy while calculating age, you may wonder how easily you can do that. In this article, I’m going to show you the Excel formula to calculate age on a specific date. For conducting the session, I’ve used Excel 2019. You can use any version.

Before diving into the bigger picture, let’s get to know about today’s practice workbook.

There are seven columns, **Name, Date of Birth**,** A Specific Date**,** Age(Basic Calculation)**, **Age Using Function(YEARFRAC)**,** Age Using Function(DATEDIF)**,** Full Age (Year-Month-Days)**. Using the data of this table we will calculate the age on a specific date.

**A Specific Date** column has the date stored as **21-Jun-21**. I’m creating this sheet on 21 June of 2021. That’s why I’m using this day.

## Practice Workbook

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

## 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 **TODAY()**.

Writing the function in Excel just hit **Enter**. You will see the magic!

The day of creating this article is 21 June 2021. So the **TODAY()** function is providing that day.

Another function I prefer mentioning here is **NOW()**.

Write the function and Hit **Enter**.

It will give you the current time of your time zone. The time of taking the screenshot is 3:26 PM on 21 June 2021. You will see your local time.

## Age Calculation

Before calculating the age let’s see one thing.

I’ve inserted the date manually here. You can insert the date like this. Or you can use **TODAY() **function.

Since I’m calculating the age on today, I’m using today’s date or **TODAY()** function. You can insert your preferable date or can use **TODAY()** if necessary.

### 1. Basic Formula to Calculate Age

To calculate age you simply need to get the difference of two dates(Date of Birth and Calculating Date).

Since age is calculated on a year basis, we 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 fraction. 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 our formula will be like,

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

It gave us the result.

If there is a situation that anyone insert a date older than the **Date of Birth**, what will happen then? To nullify that kind of situation use a **IF()** statement.

In this **IF() **statement, I have checked whether the **Date of Birth** is smaller(older) or not than the calculation date(**A Specific Date**). If yes, then calculate the age, else set to show ” **Not Born**“.

All our **Date of Birth** values here are older than the calculation date so “**Not Born**” will not be shown.

Do the same for the rest of the values or use Excel **AutoFill** Feature.

### 2. YEARFRAC() function to Calculate Age

You can calculate age using the **YEARFRAC()** function.

**YEARFRAC()** takes three parameters, **start_date**, **end_date** and **basis**.

Insert the dates in two date placeholders.

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.

So calculation using **YEARFRAC()** function will be like

**YEARFRAC(Calculation Date, Date of Birth, 3)**

Using 3 since we need to calculate using 365 days in a year.

Writing this function may give a fraction value. So let’s round this using the **ROUNDDOWN()** function.

**ROUNDDOWN()** function will take two parameters. **First**, the value you want to round, and the **second** one is how many decimal points you want.

Since we don’t want to see any decimal in age value, we will use 0.

So, the formula will be like

**ROUNDDOWN(YEARFRAC(Calculation Date, Date of Birth,3),0)**

It gave the result. Now do the same for the rest of the rows or exercise the **AutoFill** feature.

### 3. DATEDIF() function to Calculate Age

You can get the age by using the **DATEDIF()** function. **DATEDIF()** function has three parameters, **start_date**, **end_date** and **format**.

Here I’ve written the function placing **Date of Birth** and **A Specific Date** in two date placeholders and **“Y”** in type placeholder. **Y** for days, since we need age in the unit of years.

### Age representation with years, month and days

Now you may need to present the age in a format of years-month and days. Let’s see how you can do that.

We will use the **DATEDIF()** function. “**&**” is for concating. Concat means, add something with it.

At the beginning, we will calculate the difference in year format and then calculate the days, at last calculate days.

We got the age, after every **DATEDIF()** function I concat the unit within **” “**.

**YM** will give the difference between the months in** start_date** and **end_date** ignoring the days and years.

**MD** will give the difference between the days in **start_date** and **end_date** ignoring the months and years.

These type formats are not case sensitive.

Do the same for the rest of the rows or use Excel **AutoFill** feature. In the real world scenario **AutoFill** will be beneficial for handling large data sets.

## Conclusion

That’s all for today. I’ve tried listing a couple of ways to calculate age on a specific date. 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.

## Calculator

You can use today’s practice workbook as a calculator to count age. You will find a sheet called **Age Calculator **

Explore the sheet. Insert your value to the respective fields, it will give you calculated age. One example is done for your understanding.

## Further Readings

- Excel Formula to Find Date or Days in Next Month (6 Quick Ways)
- Excel Formula to Count Days from Date to Today (8 Effective Ways)
- Excel Formula to Calculate Number of Days Between Today & Another Date (6 Quick Ways)
- Excel Formula to Count Days from Date
- How to Add/Subtract Years to a Date in Excel
- How to calculate years of service in Excel (3 easy ways)