Calculating age in Excel is a simple yet essential task, whether you're tracking employee birthdays, analyzing customer data, or working on a personal project. While the DATEDIF function is commonly used for this. Excel offers several other methods, such as YEARFRAC, YEAR, and basic math formulas, to calculate age accurately in years, months, or days.

How to Calculate Age in Excel Using Date of Birth

Calculating a person’s age from their date of birth (DOB) in Excel is straightforward using simple math. This method is helpful for tracking ages in employee records, student databases, or personal planning. You can subtract the date of birth from the current date and divide the result by 365.25 to account for leap years.

So, the formula would be:

=(TODAY() - Date_of_birth)/365.25

Step 1: Enter the DOB or Start Date

Input the person’s date of birth into a cell (e.g., A2).

Step 2: Select the Cell for the Age Calculation

Choose an empty cell where you want the calculated age to appear. Here we have selected A4.

Step 3: Enter the Formula

Write the following formula in the selected cell:

In this case, the formula to calculate age would be:

=(TODAY() - A2)/ 365.25

With the current date of 26.04.2021, the age of the above person is 16 years.

Remarks:

This formula returns the age in decimal form. In order to get age in integer form, we can wrap the formula in the INT function. 

The problem with this formula is that it will produce the wrong result if you try to find the age of someone who hasn't lived through a leap year.

How to Calculate Age Using YearFrac Function

YEARFRAC function in Excel returns a decimal value that represents fractional years between two dates i.e., the number of whole days between two dates. This method is ideal for calculating exact ages in years, including fractions, such as for financial analysis, employee records, or academic purposes. It’s quick, efficient, and highly accurate!.

Syntax

= INT(YEARFRAC( date_of_birth , Today(),[Basis]))

Arguments:

• YEARFRAC: This is the inbuilt function in Excel that is used to find the age.
• date_of_birth: This is the Start date. 
• Today(): This is also an inbuilt function in Excel to find the end date.
• Basis: Use basis 1 which tells Excel to divide the actual number of days per month by the actual number of days per year. This is the optional part of the formula.

Follow the steps to calculate the age using YEARFRAC():

Step 1: Enter the Date of Birth

Input the person's DOB into a specific cell, e.g., A2.

Step 2: Select the Result Cell

Choose an empty cell where you want the calculated age to appear. Here we have selected A4.

Step 3: Write the Formula and Press Enter

Enter the following formula in the selected cell:

=INT(YEARFRAC(A2, TODAY(), 1))

How to Calculate Age Using the YEAR() Function

The YEAR() function in Excel, combined with the NOW() or TODAY() function, provides a simple and effective way to calculate age. This method works by extracting the year from the current date and subtracting the year of birth.

Follow the steps to calculate age with YEAR() and NOW()  functions

Step 1: Enter the Date of Birth

Input the person’s Date of Birth (DOB) in a cell, e.g., A2.

Step 2: Select the Result Cell

Choose a blank cell where you want the calculated age to appear.

Step 3: Enter the Formula

Type the following formula in the result cell:

=YEAR(NOW()) - YEAR(A2)

This formula calculates the difference between the current year (using YEAR(NOW())) and the year of birth (using YEAR(A2)).

How to Calculate Age Using Excel's Date Functions

In Excel, you can calculate age with greater precision by using built-in date functions like YEAR(), MONTH(), and DAY(). These functions allow you to account for differences in years, months, and even days between two dates. 

Formula: 

IF (DAY(A2)>=DAY(A1),0 , -1)+ (YEAR(B2) - YEAR(A1))*12 + MONTH(A2) - MONTH(A1) &" months old"

Purpose of Each Function

• DAY(): Returns the day of the month (1–31) for a given date.
• MONTH(): Returns the month (1–12) for a given date.
• YEAR(): Returns the year for a given date.

Step 1: Enter the Dates

• Input the Date of Birth in cell A1.
• Input the Current Date (or the date you want to calculate the age for) in cell A2.

Step 2: Enter the Formula

• Select a blank cell where you want to display the age.
• Enter the formula

=IF(DAY(A2) >= DAY(A1), 0, -1) + (YEAR(A2) - YEAR(A1)) * 12 + MONTH(A2) - MONTH(A1) & " months old"

Step 3: Press Enter

• Excel will calculate the age in months and display it with the text "months old."

How to Create an Age Calculator in Excel

An age calculator in Excel is a useful tool for determining the age of a person or item based on their date of birth or start date. With simple formulas, you can calculate age in years, months, or days. Follow the steps below to create an effective age calculator in Excel.

Step 1: Prepare Your Data

• Enter the Name, Date of Birth, and Current Date in separate columns as shown in the table:
  • Column A: Names
  • Column B: Date of Birth (e.g., 01/15/1990 for John Smith).
  • Column C: Current Date (e.g., 01/20/2025 for all entries or use the formula =TODAY() for a dynamic current date).

Step 2: Calculate Age in Years Using DATEDIF

• Select a blank cell where you want the age to appear.
• Enter the following formula to calculate the age in years

=DATEDIF(A2, TODAY(), "Y")

• A2: The cell containing the date of birth.
• TODAY(): Automatically retrieves the current date.
• "Y": Calculates the total years between the two dates

Step 3: Calculate Age in Months

To find the total number of months since the date of birth, use this formula:

=DATEDIF(A2, TODAY(), "YM")

"YM": Calculates the remaining months after accounting for completed years.

Step 4: Calculate Age in Days

To calculate the exact number of days since the date of birth, use:

=DATEDIF(A2, TODAY(), "D")

"D": Returns the total days between the two dates.

Step 5: Combine Years, Months, and Days

If you want a more detailed result that includes years, months, and days, combine formulas:

=DATEDIF(A2, TODAY(), "Y") & " Years, " & DATEDIF(A2, TODAY(), "YM") & " Months, " & DATEDIF(A2, TODAY(), "MD") & " Days"

"MD": Calculates the remaining days after completed months.

Ready-to-Use Age Calculator Template

You can create an age calculator template in Excel by formatting the data as follows:

• Column A: Date of Birth.
• Column B: Current Date (or leave it blank to use TODAY()).
• Column C: Age in Years (=DATEDIF(A2, TODAY(), "Y")).
• Column D: Age in Detailed Format (=DATEDIF(A2, TODAY(), "Y") & " Years, " & DATEDIF(A2, TODAY(), "YM") & " Months, " & DATEDIF(A2, TODAY(), "MD") & " Days").