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.
Table of Contents
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.
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.
Enter the TODAY function into cell B2 to return today’s date.
The DATEDIF function calculates an individual's age.
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”.
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.
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
This is absolute new for me.
You very well simplified the steps.
Going to be very useful one along with saving time.
Thank you Mam!