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.
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:
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.
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.
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)
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))
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)
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:
As shown previously, DOB is in Cell C3, DATEDIF formula to calculate age by year is as shown:
=DATEDIF(C3, TODAY(), "y")
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")
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.