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.

1. To calculate age in years, we use the below-given DATEDIF function.

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".

2. To display age in years and months, we use the below-mentioned DATEDIF function.

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 -

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.

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.

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 -

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.