Age Calculation Formula in Excel

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

Calculating an individual’s age on Excel is helpful in several situations. Mainly, the HR department uses the age calculation formula in Excel to calculate an employee's retirement date and find their pension based on the number of years spent working for your company.

Age calculation is also necessary for lots of business and other operations. Whether you want to calculate the age of an individual 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.
• 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")

That's it! We have covered several ways to calculate age in Excel. We hope this tutorial was helpful to you. If you have any questions or feedback, please let us know in the comments box below.

You May Also Like the Following Excel Tutorials:

• How to do Addition in Excel?
• Transpose in Excel
• How to do Percentage in Excel?
• How to do Rank in Excel?
• How to do Freeze in Excel?

IF YOU LIKE OUR EXCEL TUTORIALS PLEASE DON'T FORGET TO SHARE

Subscribe for more videos on our Youtube channel. if you like our tutorial please Like and Subscribe to our Facebook and Youtube.