How to Calculate Age in Excel using Formulas

You are currently viewing How to Calculate Age in Excel using Formulas

In this tutorial, you`ll discover ways to calculate age in Excel in: The quantity of years elapsed until the desired date. The quantity of Years, Months, and Days elapsed until the desired date.

Although there is no dedicated function in Excel to compute age, there are a few alternative ways to convert date of birth to age. This video will walk you through the benefits and cons of each method, as well as how to create a perfect age calculation formula in Excel and customize it to solve specific problems.

How to Calculate Age in Excel

In ordinary life, the query “How old are you?” normally elicits a response expressing your age in years. You may create a formula in Microsoft Excel to calculate an exact age in months, days, hours, and even minutes. But let’s start with the basics and learn how to convert DOB to years.

Basic Excel formula for age in years

How do you typically determine someone’s age? Simply subtract the current date from the birth date. In Excel, you may utilize the traditional age formula.

Given a birthday in cell B2, the following formula is used to compute age in years:

=(TODAY()-B2)/365

The difference between the current date and the date of birth is returned by the first half of the formula (TODAY()-B2), which you divide by 365 to get the years.

The method is simple and easy to memorise, however there is one little flaw. It typically returns a decimal number, as shown in the picture below.

Calculate Age In Years

Use DATEDIF and TODAY to calculate a person’s age in Excel. The DATEDIF function takes three arguments.

Enter your date of birth in cell A2.

Calculate Age In Excel

Enter the TODAY function into cell B2 to return today’s date.

Today function in excel

The DATEDIF function calculates an individual's age.

Datedif Function

Calculate the age of people without displaying today's date

In this example we are going to calculate age without displaying today’s date separately. Instead of displaying todays date we will use excel today() function inside our DATEDIF excel formula as per shown in below example.

Learn More About DATEDIF Function

In this formula it will automatically calculate years between date mentioned in A2 cell and todays date. And will show output in user defined format, in this case Years as we mentioned “y”. 

without today function

Calculate the person's age on a specific date.

In this will mention a specific date on the basis of which it will calculate an age of individual.

In below example birth date is 21/10/1981 and we specified date in DATEDIF function is 21/10/2021. so its shown output 40 years as we asked to show age in years.

specified date age in excel

The following age formula calculates a person's age in years, months, and days.

In this we are going o calculate age using DATEDIF function with additional & function that’s why DATEDIF function divided into multiple conditions. 

=datedif(a2,b2,"y") & "y" & datedif(a2,b2,"ym") & "m" & datedif(a2,b2,"md") & "d"

As showing in the below example first we calculated years and attached string to output “yrs” after displaying of age in years by using  & function. Same way we calculate  months and days of the age and attached m & d strings after the output.

So final result look like 40yrs 7m 6d.

So in this article we learned various methods of calculating our Age. Using this same method you can able to count days months years between any two given dates, which will useful in various excel reports.

If you like this article and find useful please share your comment in below comment box.

If you have any queries or doubts please feel free to connect with me at : abhijit@mygeekshub.com

Also Read: How Get the Day From Date In Excel

 

Abhijit

Abhijit currently working with India's No. 1 Ecommerce Company Flipkart. He having 6 years experience in Excel and he is also expertise in various Advance Excel formulas. He awarded by his organization for his outstanding contribution in Analytical Project.
5 1 vote
Article Rating
2 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Rupjyoti Phukan
Rupjyoti Phukan
1 year ago

This is absolute new for me.

You very well simplified the steps.
Going to be very useful one along with saving time.