Excel formula to Calculate Age on a Specific Date

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.

Excel sheet- Excel formula to Calculate Age on a Specific Date

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().

TODAY-Excel formula to Calculate Age on a Specific Date

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

TODAY Presents-Excel formula to Calculate Age on a Specific Date

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().

NOW - Excel formula to Calculate Age on a Specific Date

Write the function and Hit Enter.

NOW format-Excel formula to Calculate Age on a Specific Date

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.

Date Insert Manually -Excel formula to Calculate Age on a Specific Date

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

Date Insert function -Excel formula to Calculate Age on a Specific Date

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

INT age calculation - Excel formula to Calculate Age on a Specific Date

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.

Logic before calculate age - Excel formula to Calculate Age on a Specific Date

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.

AutoFill age - Excel formula to Calculate Age on a Specific Date

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)

YEARFRAC age calculation - Excel formula to Calculate Age on a Specific Date

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

AutoFill age calculation - Excel formula to Calculate Age on a Specific Date

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.

DATEDIF age Calculation - Excel formula to Calculate Age on a Specific Date

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.

 

AutoFill Datedif calculation - Excel formula to Calculate Age on a Specific Date

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.

Age Full - 18.Excel formula to Calculate Age on a Specific DateWe 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.

Fill full age - 18.Excel formula to Calculate Age on a Specific Date

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 

Calculator sheet - 18.Excel formula to Calculate Age on a Specific Date

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

Calculate age - 18.Excel formula to Calculate Age on a Specific Date

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo