How to Calculate Age on a Specific Date with Formula in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.


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.

Calculate Age on a Specific Date with Formula in Excel


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)

Use Generic Formula with INT Function to Get Age on a Specific Date

  • After that, press Enter to get the age.

Using Generic Formula with INT Function in Excel Formula to Calculate Age on a Specific Date

🔎 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.
  • Next, drag the Fill Handle down to copy the formula.

Dragging Fill Handle to Copy Excel Formula to Calculate Age on a Specific Date

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

Employ IF Function to Calculate Age on a Specific Date in Excel Formula

  • 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.

Read More: How to Convert Date of Birth to Age in Excel


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 a ge. Here, I selected cell E5.
  • Next, in cell E5 write the following formula.
=ROUNDDOWN(YEARFRAC(C5,D5,3),0)

Age Calculation on a Specific Date with Excel YEARFRAC Function

  • Afterward, press Enter.

Using YEARFRAC Function in Excel Formula to Calculate Age on a Specific Date

🔎 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.

Applying ROUNDDOWN Function to Calculate Age on a Specific Date in Excel Formula

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

Read More: How to Calculate Age in Excel in dd/mm/yyyy


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

Employ Excel Formula with DATEDIF Function to Calculate Age on a Specific Date 

  • Thirdly, press Enter to get the result.

Here, in the 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"

Determine Age with Years, Months, and Days in Excel

  • Next, press Enter to get the age.

Using DATEDIF Function in Excel formula to Calculate Age with Years Months and Days on a Specific Date

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

How to Calculate Age from Date of Birth in Excel

  • 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.

Using TODAY Function in Excel Formula to Calculate Age from a Specific Date of Birth

  • 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


<< Go Back to Calculate Age | Date-Time in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shakil Ahmed
Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo