How to Calculate Age in Excel using Formulas

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

How to Calculate Age in Excel - Datedif years

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

Now, let’s see how the DATEDIF function works.

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 tool tip appears when you use this function.

How to Calculate Age in Excel - Datedif

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

Overview of the Excel DATEDIF function

Syntax: =DATEDIF(start_date, end_date, unit)

Excel DATEDIF function 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 number, 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 number, 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()))

How to Calculate Age in Excel - YEARFRAC years

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

How to Calculate Age in Excel - Datedif Year Value

To get the month value:

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

How to Calculate Age in Excel - Datedif Month Value

To get the day value:

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

How to Calculate Age in Excel - Datedif Day Value

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"

How to Calculate Age in Excel - combined

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
Download File

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: