How to Calculate Age in Excel using Formulas + FREE Calculator Template

Watch Video – How to Calculate Age in Excel (in Years, Months, and Days)

Using a combination of Excel functions and the date of birth, you can easily calculate age in Excel. You can either calculate the age till the current date or between the specified period of time.

The technique shown here can also be used in other situations such as calculating the duration of a project or the tenure of the service.

How to Calculate Age in Excel

In this tutorial, you’ll learn how to calculate age in Excel in:

• The number of years elapsed till the specified date.
• The number of Years, Months, and Days elapsed till the specified date.

You can also download the Excel Age Calculator Template.

Calculate Age in Excel – Years Only

Suppose you have the date of birth in cell B1, and you want to calculate how many years have elapsed since that date, here is the formula that’ll give you the result:

=DATEDIF(B1,TODAY(),"Y")

If you have the current date (or the end date) in a cell, you can use the reference instead of the TODAY function. For example, if you have the current date in cell B2, you can use the formula:

=DATEDIF(B1,B2,"Y")

DATEDIF function is provided for the compatibility with Lotus 1-2-3.

One of the things that you’ll notice when you use this function is that there is no IntelliSense available for this function. No tooltip appears when you use this function.

This means that while you can use this function in Excel, you need to know the syntax and how many arguments this function takes.

If you’re interested in knowing more about DATEDIF function, read the content of the box below. If not, you can skip this and move to the next section.

Syntax of DATEDIF function:

=DATEDIF(start_date, end_date, unit)

It takes 3 arguments:

• start_date: It’s a date that represents the starting date value of the period. It can be entered as text strings in double-quotes, as serial numbers, or as a result of some other function, such as DATE().

• end_date: It’s a date that represents the end date value of the period. It can be entered as text strings in double-quotes, as serial numbers, or as a result of some other function, such as DATE().

• unit: This would determine what type of result you get from this function. There are six different output that you can get from the DATEDIF function, based on what unit you use. Here are the units that you can use:

  • “Y” – returns the number of completed years in the specified time period.

  • “M” – returns the number of completed months in the specified time period.

  • “D” – returns the number of completed days in the specified period.

  • “MD” – returns the number of days in the period, but doesn’t count the ones in the Years and Months that have been completed.

  • “YM” – returns the number of months in the period, but doesn’t count the ones in the years that have been completed.

  • “YD” – returns the number of days in the period, but doesn’t count the ones in the years that have been completed. 

You can also use the YEARFRAC function to calculate the age in Excel (in years) in the specified date range.

Here is the formula:

=INT(YEARFRAC(B1,TODAY()))

The YEARFRAC function returns the number of years between the two specified dates and then the INT function returns only the integer part of the value.

NOTE: It’s a good practice to use the DATE function to get the date value. It avoids any erroneous results that may occur when entering the date as text or any other format (which is not an acceptable date format).

Calculate Age in Excel – Years, Months, & Days

Suppose you have the date of birth in cell A1, here are the formulas:

To get the year value:

=DATEDIF(B1,TODAY(),"Y")

To get the month value:

=DATEDIF(B1,TODAY(),"YM")

To get the day value:

=DATEDIF(B1,TODAY(),"MD")

Now that you know how to calculate the years, months and days, you can combine these three to get a text that says 26 Years, 2 Months, and 13 Days. Here is the formula that will get this done:

=DATEDIF(B1,TODAY(),"Y")&" Years "&DATEDIF(B1,TODAY(),"YM")&" Months "&DATEDIF(B1,TODAY(),"MD")&" Days"

Note that the TODAY function is volatile and its value would change every day whenever you open the workbook or there is a change in it. If you want to keep the result as is, convert the formula result to a static value.

Download the Excel Age Calculator Template

Excel Functions Used:

Here is a list of functions used in this tutorial:

• DATEDIF() – This function calculates the number of days, months, and years between two specified dates.
• TODAY() – It gives the current date value.
• YEARFRAC() – It takes the start date and the end date and gives you the number of years that have passed between the two dates. For example, if someone’s date of birth is 01-01-1990, and the current date is 15-06-2016, the formula would return 26.455. Here the integer part represents the number of years completed, and the decimal part represents additional days that have passed after 26 years.
• DATE() – It returns the date value when you specify the Year, Month, and Day value arguments.
• INT() – This returns the integer part of a value.

You May Also Like the Following Excel Tutorials: