How to calculate age in Excel
Microsoft allows us to calculate a person’s age in years, months, as well as days if we know the person’s date of birth.
There are the following methods to calculate age in Excel -
Method 1: Calculate age using the DATEDIF Function
DATEDIF is an in-built, simplest, and most accurate [Microsoft Excel] function that helps us to calculate a person’s age using two parameters, date of birth and current date . After calculation, It returns age in years, months, and days .
- To calculate age in years, we use the below-given DATEDIF function.
=DATEDIF(A1,TODAY(),“unit”)
Where, A1 cell represents a person’s date of birth.
Unit - the unit of time use (years, months, or days) such as “y”, ‘‘m’’, “d”, “ym”, “md”, and “yd”.
- To display age in years and months, we use the below-mentioned DATEDIF function.
=DATEDIF(A1,TODAY(),“y”)&" years “&DATEDIF(A1,TODAY(),“ym”)& " months”
Example 1:
Follow the below steps to calculate the age of a person in years -
Step 1: Open a new Microsoft Excel document.
Step 2: Enter a date of birth into the desired cell.
Note: We use cell A2 to enter the date of birth.
Step 3: Enter the current date in cell B2.
Step 4: In cell C2, type =DATEDIF(A2,B2,“y”) to calculate the age of a person.
Note: You can also use =DATEDIF(A2,TODAY(),“y”) formula to calculate age in years without entering the current date.
Step 5: Press the Enter key from the keyboard. You can see that age is calculated in the cell C2, as shown in the screenshot below.
Example 2:
Calculate age in years and months
To calculate age in years and months, we use the below syntax -
Type =DATEDIF(date_of_birth,TODAY(),“y”)&" years"&DATEDIF(A1,TODAY(),“ym”)& " months"
The below given easiest steps are used to calculate age in years and months.
Step 1: Open a new or existing Microsoft excel document where you want to calculate the age of a person.
Step 2: Enter the date of birth of a person.
Step 3: Place the cursor in the cell where you want to display age in years and months.
Step 4: Type =DATEDIF(A1,TODAY(),“y”)&" years"&DATEDIF(A1,TODAY(),“ym”)& " months" in selected cell.
Step 5: Press the Enter key from the keyboard. You can see that age is calculated in years and months.
Method 2: Calculate age using a Formula
In Microsoft Excel, we use the below formula to calculate the age of a person.
=ROUNDDOWN((A2-B2)/365.25,0)
Where A2 = Current Date
B2 = Person’s Date of Birth
ROUNDDOWN function is used to round down the decimal places.
Note: 365.25 is used for a leap year, which appears every 4 years.
There are the following easiest steps to calculate the age of a person using a Formula.
Step 1: Open a new Microsoft Excel document.
Step 2: Enter the current date and person’s date of birth in the respective cells.
Step 3: Place the cursor in the cell where you want to display age and type =ROUNDDOWN((A2-B2)/362.25,0) to calculate the age of a person.
Note: If you don’t use the ROUNDDOWN function in the formula, then age will contain a decimal value.
Step 4: Press the Enter key from the keyboard, you can see that the age of a person displayed on your desired cell.
Note: In our case, the age is displayed in cell C2.
Method 3: Calculate age using YEARFRC function
YEARFRAC is one of the simplest and commonly used methods to calculate the age of a person in an Excel document. We use the below syntax to calculate age using the YEARFRAC function.
YEARFRAC(Date_of_Birth,Current_date)
Note: YEARFRAC function calculates age in a decimal value, so we use the YEARFRAC function along with the INT function to calculate age in the integer value.
Use the below-given syntax to calculate age using the YEARFRAC function -
=INT(YEARFRAC(Date_of_Birth,Current_date))
Steps to calculate age using the YEARFRAC
Step 1: Open the new or an existing Excel document.
Step 2: Type the date of birth and current data in the cell.
Step 3: Type =INT(YEARFRAC(B2,A2)) function in the cell where you want to display the age of a person, as shown in the screenshot below.
Step 4: Press the Enter key . You can see that age of a person is displayed on the excel document.
