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. For example, you may just require the age in Years or age in months and years.

Age by DATEDIF Interactive Demo

DATEDIF Years/Months Interactive Demo

YEARFRAC() Interactive Demo

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

Do Interactive Exercise Online

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

Similarly, the B2 cell assigned the following formula:

1	=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:

Do Interactive Exercise Online

Just replace the “A4” by your Date of Birth cell and get the age in Year/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:

1	=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 NOW() function. See the formula and excel sheet with DOB column and age:

Age formula

Do Interactive Exercise Online

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 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 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 for getting the number of days since the date of birth:

The formula of DATEDIF:

 

Getting number of months example by DATEDIF

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

Formula: