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 how 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 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

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,

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

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

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.

The post Excel formula to Calculate Age on a Specific Date appeared first on ExcelDemy.