Age Calculation in Excel: Microsoft Excel is widely used spreadsheet software that comes with built-in tools and features. It helps us to record different data and perform calculations across them with multiple cell.
If you have a excel sheet containing a number of birth dates, and you want to know the current age of those people, excel makes it easy to calculate a person’s age from their birth date.
You have several options for doing this. First, you can calculate age in years, even you can calculate age in years, months and days. In this tutorial, we show different ways to get age from birth date in excel. Today you will learn some excel formulas to calculate age in excel.
Table of Contents
Age Calculation in Excel Formula
There are various ways and formulas in Excel to calculate someone’s age. To calculate age in Excel using a formula, you can use the following formula:
01. Using the DATEDIF Function:
=DATEDIF(birth_date,TODAY(),"Y")
Replace ‘birth_date’ with the cell reference or the actual date of birth. For Example, assuming the date of birth is in cell A2, you would use the following formula:
=DATEDIF(A2,TODAY(),"Y")
This formula calculates the difference between the date of birth (A2) and the current date (TODAY()) in years (“Y”).
02. Using the YEARFRAC Function:
=INT(YEARFRAC(birth_date, TODAY()))
=INT(YEARFRAC(A3, TODAY()))
=YEARFRAC(birth_date, TODAY())-INT(YEARFRAC(birth_date, TODAY()))
=YEARFRAC(A4, TODAY())-INT(YEARFRAC(A4, TODAY()))
=YEAR(TODAY()) - YEAR(birth_date) - IF(OR(MONTH(birth_date) > MONTH(TODAY()), AND(MONTH(birth_date) = MONTH(TODAY()), DAY(birth_date) > DAY(TODAY()))), 1, 0)
=YEAR(TODAY()) - YEAR(A5) - IF(OR(MONTH(A5) > MONTH(TODAY()), AND(MONTH(A5) = MONTH(TODAY()), DAY(A5) > DAY(TODAY()))), 1, 0)
How to Calculate Age in Years?
You can use different Excel functions to calculate someone’s age from their date of birth. If you want to calculate a person’s current age from their date of birth, the best choice is to use the DATEDIF function or the YEARFRAC function.
01. DATEDIF Function:
The DATEDIF function calculates the difference between dates in years, months, or days. To calculate age in years, you can use the “Y” parameter with the DATEDIF function. For Example,
In the following spreadsheet, the date of birth is specified in the B2 cell, we will display the current age in the C2 cell. So here is the step to calculate age using DATEDIF function:
- Step 1: Click the C2 cell where we want to show the age of the person in years.
- Step 2: In the C2 cell, we will write the following function and press ‘Enter’. In this function, cell B2 refers to the birth date, “TODAY()” finds today’s date, and ‘Y’ indicates that you want to see the age in years.
=DATEDIF(B2,TODAY(),"Y")
- Step 3: You will see the person’s age in the C2 cell immediately.
02. YEARFRAC Function:
The year function extracts the year from the given date. You can use this function along with TODAY function. This formula subtracts the birth year (YEAR(A2)) from the current year (YEAR(TODAY())) to calculate the age in years.
Here is the step to calculate age using YEARFRAC function:
- Step 1: Open Excel and click the C2 cell where we want to show the age of the person in years.
- Step 2: In the C2 cell, we will write the following function and press ‘Enter’. In this function, cell B2 refers to the birth date, and B1 refers to the current date.
=INT(YEARFRAC(birth_date, TODAY()))
- Step 3: You will see the person’s age in the C2 cell immediately. Drag down to apply the formula to other cells.
Use This Tool For Age Calculation:- Age Calculator From Date of Birth
How to Calculate Age in Months in Excel?
There are different methods for age calculation in Excel. You can use the DATEDIF function to calculate someone’s age in months. Just you have to change the unit in the formula from ‘Y’ to ‘M’, which tells Excel to display or return age in months. Here it is:
=DATEDIF(B2,TODAY(),"M")
For Example, we will use the following spreadsheet, where B2 cell specifies the date of birth, and we will display the age in the C2 cell.
How to Calculate Age in Days in Excel?
Again we can use the DATEDIF function for age calculation in Excel in days. In the previous example, we changed the unit argument from ‘Y’ to ‘M’, similarly we will change the argument from ‘M’ to ‘D’, which will return the age in days. Here it is:
=DATEDIF(B2,TODAY(),"D")
For Example, we will use the following spreadsheet, where B2 cell specifies the date of birth, and we will display the age in days in the C2 cell.
How to Calculate Age in Years, Months, and Days in Excel?
To get a person’s age in years, months, and days in Excel, again you can use the DATEDIF function with combination of all the arguments. Here is the following function:
=DATEDIF(B2,TODAY(),"Y")&DATEDIF(B2,TODAY(),"YM")&DATEDIF(B2,TODAY(),"MD")
How to Calculate Age on a Specific Date?
Age calculation in excel using the DATEDIF function is very easy. Just you have to type the following command:
=DATEDIF(Birth_Date,Specific_Date,"Y")
For Example, if you have the birth date in cell A2 and you want to display the age in C2 cell, then you can use the above mentioned formula, where cell A2 refers to the birth date, and B2 refers to the current date.
