Age Calculation Formula in Excel

Age Calculation by DOB | YEARFRAC() function | DATEDIF()

Calculating age in Excel is helpful in several situations. Calculating an individual's age is an important process in the HR department. Age calculation is also necessary for lots of business and other operations.

You can also use Excel to calculate an employee's retirement date and find their pension based on the number of years spent working for your company. Whether you want to learn age calculation formula in Excel for work or experiment with Excel formulas, this tutorial will teach you various ways to determine someone's age in Excel.

1. Age Calculation using Date of Birth

You can calculate the age of an individual by their birthday.

The best and easiest way to calculate someone's age is to subtract the date of birth from the current date. That is how we manually determine a person's age, isn't it? The same formula can be used in Excel.

Let's say the birthdate is in cell C3. The age calculation in Excel formula is as follows:

=(TODAY()-C3)/365

Using the formula TODAY()-C3, you get the difference between the current date and your date of birth in days, then divide that number by 365 for years.

However, this formula results in decimal numbers as shown below.

The INT function can be used to round it down to the nearest integer.

=INT((TODAY()-C3)/365)

The problem with this age formula is that it is based on the assumption that every year is exactly 365 days.

As every fourth year (leap year) has 366 days, you divide the date by 365.25, but even this is not guaranteed to be accurate.

Hence this formula works well in a few cases but is flawed when the person is born during a leap year.

2. Age Calculation using YEARFRAC() function

The YEARFRAC function in Excel provides an accurate age calculation formula in Excel. With the YEARFRAC function, you can calculate age from date of birth that returns the fraction of the year for two dates.

Here is the Excel formula for the YEARFRAC function:

YEARFRAC(DOB, TODAY(), Basis)

Here,

Select Basis = 1, which tells Excel to divide the number of days per month by the number of days per year.

As shown in the previous example, DOB is in cell C, hence the formula to be used is

=YEARFRAC(C3, TODAY(), 1)

The decimal number can be rounded down using the INT function,

=INT(YEARFRAC(C3, TODAY(), 1))

3. Age Calculation using the DATEDIF() function

DATEDIF() function is one of the most popular age calculation formula in Excel. Let us now see how to use this function to calculate age in years, months, and days.

The syntax of the DATEDIF function is

=DATEDIF(start_date, end_date, unit)

Here,

Start_date - This can be the date of birth.

End_date - This can be the current date, TODAY().

Unit - The DATEDIF function can produce six different sets of results, depending on what unit you use. Here is a complete list of the units you can use:

Y – indicates the number of completed years in the specified period.
M – indicates the number of completed months in the specified period.
D – indicates the number of completed days in the specified period.MD – counts how many days are in the period, but does not include the ones in the Years and Months that have already passed.
MD – counts how many days are in the period, but does not include the ones in the Years and Months that have already passed.
YM – indicates the number of months in the period, but does not include the ones in the Years and Months that have already passed.
YD – indicates the number of days in the period, but doesn’t count the ones in the Years that have already passed.

Calculating age by year

As shown previously, DOB is in Cell C3, DATEDIF formula to calculate age by year is as shown:

=DATEDIF(C3, TODAY(), "y")

Calculating age by year, months, and days

Similar to the year formula, the DATEDIF function can be used to calculate age by year, months, and days using the following formula:

=DATEDIF(C3,TODAY(),"Y") & DATEDIF(C3,TODAY(),"YM") & DATEDIF(C3,TODAY(),"MD")

where

1. =DATEDIF(C3, TODAY(), "Y") - Gives the number of years
2. =DATEDIF(C3, TODAY(), "YM") - Gives the number of months
3. =DATEDIF(C3,TODAY(),"MD") - Gives the number of days

Since the result shown in the above image does not make any sense, let us add commas and text to help differentiate year, month, and dates using the IF function.

=IF(DATEDIF(C3, TODAY(),"y")=0,"",DATEDIF(C3, TODAY(),"y")&" years, ")& IF(DATEDIF(C3, TODAY(),"ym")=0,"",DATEDIF(C3, TODAY(),"ym")&" months, ")& IF(DATEDIF(C3, TODAY(),"md")=0,"",DATEDIF(C3, TODAY(),"md")&" days")