Hit enter after type your search item
Home / VBA / Excel Tutorials / 4 Ways of How to Calculate Age in Excel (Years, Months and Days)

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:


Excel age calculate
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:

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:


Excel-age calculate years month
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:

The result

age-calculate years month days

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


age calculate YEARS function
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())

Excel YEARFRAC age

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:

Excel age days

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:


 

Excel age days

Getting number of months example by DATEDIF

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

Formula:


 

Excel age Months

This div height required for enabling the sticky sidebar