Calculating age in Excel is a simple yet important 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, plenty of other methods are available to calculate age accurately. Excel offers several built-in formulas like YEARFRAC, YEAR, and even basic math calculations to help you find age in years, months, or days.
In this guide, we'll show you different, easy-to-follow methods to calculate age in Excel. Whether you're just starting with Excel or already know your way around, you'll find step-by-step instructions and examples to make age calculation quick and easy.
How to Calculate Age in ExcelHow 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).
Enter the DOBStep 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.
Write the following formula in the selected cell:
In this case, the formula to calculate age would be:
=(TODAY() - A2)/ 365.25
Select the Cell>> Enter the Formula>> Press EnterWith 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.
Enter the following formula in the selected cell:
=INT(YEARFRAC(A2, TODAY(), 1))
Write the Formula and Press EnterHow 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.
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)).
Enter DOB>> Select a Cell for Result>> Enter the Formula>> Press EnterHow 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.
- 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."
Enter the Date >> Enter the Formula >> Press enterHow 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").
Similar Reads
How to Calculate Time in Excel: Step by Step Guide with Formulas Working with time in Excel can make tasks like tracking hours, calculating time differences, or managing schedules much easier. Excel provides simple tools to calculate time, figure out time differences, and even add or subtract hours using formulas. In this guide, you’ll learn about time formatting
9 min read
How to Create a Form in Excel - A Step by Step Guide Creating forms in Excel is an efficient way to capture and organize data, whether you’re tracking inventory, collecting survey responses, or entering client information. Excel forms are customizable and easy to use, making data entry faster and more organized, especially when handling large datasets
7 min read
How to Calculate Running Total in Excel? Excel is a tool widely used by professionals for financial data or trend analysis but can be used for different purposes. It is used for various data visualization purposes. It simplifies data management and data evaluation which uses spreadsheets for managing, storing, and visualizing large volumes
2 min read
How to Add a Column in Excel: Step-by-Step Guide Need to organize your data better in Excel but unsure how to add a column without disrupting your existing setup? Whether you’re working with a simple list or a complex table, inserting columns is a fundamental skill that increases productivity and keeps your data structured.This guide covers 4 easy
6 min read
How To Calculate Variance in Excel If you're looking to understand your data better, knowing how to calculate variance in Excel is a great skill to have. Calculating Variance in Excel is a Fundamental calculation that helps to measure the dispersion or spread of data points in a dataset. Luckily, Excel makes it easy to do this with s
7 min read