4 Ways of How to Calculate Age in Excel (Years, Months and Days)

Formulas to Calculate Age in Excel

In Excel, you may calculate the age in a number of ways. It is up to your choice and requirements that which way to use it. For example, you may just require the age in Years or age in months and years.

In other scenarios, you may even require calculating the age in days/months and years. In this tutorial, you will see different ways of getting the age by different formulas.

Using the DATEDIF function for getting the age in Years

The first example uses the DATEDIF function for calculating the age based on a given date of birth and the current date. The DATEDIF() takes three parameters and may be used for calculating the years as follows:

The DATEDIF age formula:

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

You can see two columns in the Excel sheet. One is the Birth Date and the other displays the calculated age based on the current day value by using the TODAY() function.

Similarly, the B2 cell is assigned the following formula:

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

and so on.

Calculating the month and year example by DATEDIF

The following example displays the months along with years from DOB.

The age month/year formula:

=DATEDIF(A4,TODAY(),"y") &" Years / "&DATEDIF(A4,TODAY(),"YM") &" Month(s)"

Just replace the “A4” with your Date of Birth cell and get the age in Years/Months.

Getting days by DATEDIF along with Year/Months

The formula below shows calculating the days as well in addition to months and years.

Age formula for Year/Month and Days:

=DATEDIF(A6,TODAY(),"y") &" Years / "&DATEDIF(A6,TODAY(),"YM") &" Month(s) / " &DATEDIF(A6,TODAY(),"MD") &" Days"

The result

Using the YEAR() function for calculating age

You may also use the YEAR() function for calculating the age with the NOW() function. See the formula and Excel sheet with the DOB column and age:

Age formula

=(YEAR(NOW())-YEAR(A3))

The YEAR() function gets the year from NOW() and A3 cell and subtracts the two dates. The returned result is the number of years of age.

You may also use the TODAY() function in place of the NOW() function. For example:

=(YEAR(TODAY())-YEAR(A3))

You may also use a year directly there:

=(YEAR(TODAY())-1992)

OR

=(YEAR(NOW())-1992)

Both returns 25 (considering current year is 2017).

Using YEARFRAC() function for age in fractions

The YEARFRAC() function can be used to get the age with a fraction. See the demonstration below with the age calculation formula:

The YEARFRAC formula for age:

=YEARFRAC(A2,TODAY())

You may also round the age like 38.19722222, 25.14166667 by using INT function as follows:

=INT(YEARFRAC(A2,TODAY()))

Calculate the number of days

For calculating the number of days since the date of birth, you may use the following ways.

Simple subtraction of two dates:

For example, we have A column containing DOB. In the formula, I will minus TODAY() with A2 or A3 cell to get the number of days as follows:

Age days calculation formula:

=(TODAY()-A2)

The resultant sheet:

Days of age by DATEDIF function

You may also use the DATEDIF function to get the number of days since the date of birth:

The formula of DATEDIF:

=DATEDIF(A2,TODAY(),"D")

Getting the number of months example by DATEDIF

The formula for getting the number of months of age by using DATEDIF.

Formula:

=DATEDIF(A6,TODAY(),"M")