How to Calculate Age in Excel?

Calculating age in MS Excel is very simple, thanks to its built-in functions and formulas. Whether you are managing employee records, analyzing demographics, or just curious about someone's age, MS Excel has the tools you need. This guide will show you how to use Excel formulas to quickly and accurately calculate ages.

Table of Content

• YEARFRAC Function
• DATEDIF Function
• ROUNDDOWN Function
• INT Function

Calculate Age in Excel Using the YEARFRAC Function

Follow these steps To calculate age from the date of birth in Excel. This method uses the YEARFRAC function -

Step 1 - First, make sure that the birth dates in your workbook are in the correct date format. Excel should recognise these values as dates, not text.

Step 2 - Use the TODAY() function to get the current date. 

Step 3 - Navigate to the cell where you want the calculated age to appear. For example, to calculate the age in cell D2 based on the birth date in cell B2 and today’s date in C2, click on cell D2.

Step 4 - Type in the formula: =(YEARFRAC(B2, C2, 1))

• • Here, B2 is the cell containing the birth date.
  • The 1 in the YEARFRAC function indicates that the calculation uses the actual number of days in the months and leap years, providing a more accurate age calculation.

Step 5 - Press Enter to apply the formula. Excel will calculate the age based on the birth date in B2 and display it in cell D2.

If you encounter this issue, go to Number Formats in the Ribbon and change from Date => General.

Step 6 - Copy the Formula for Other Cells: You have a list of birth dates in column B. To calculate the ages, drag the fill handle (a small square at the bottom right corner of cell C2) down to fill the formula into other cells in column C. Excel will automatically adjust the formula for each row.

If you want the round-off age of a person, use the YEARFRAC function combined with the INT function to calculate the age in whole years, accounting for leap years and the actual number of days each month.

Here is the formula - 

=INT(YEARFRAC(B2, C2, 1))

You can also calculate a person's age on a given date (past or future), not just today’s date. Just play around and use this formula - 

Formula Breakdown: =INT(YEARFRAC(B2, DATE(2050,6,22), 1))
This formula calculates age as of June 22nd, 2050, based on a birthdate in cell B2:

Function Breakdown:

• INT(): Truncates any decimal part, returning the whole number representing age in years.

YEARFRAC(B2, DATE(2050,6,22), 1)

• YEARFRAC: Calculates the fraction of a year between two dates.
• B2: Cell containing the birthdate.
• DATE(2050,6,22): The reference date is set for June 22nd, 2050.
• 1: Specifies to consider full years only (ignoring days and months).

Note

• Using the formula, you can avoid adding a new column for today's date. =INT(YEARFRAC(B2, TODAY(), 1))

• Accuracy: The =INT(YEARFRAC method calculates an accurate age by considering the exact number of days each month and accounting for leap years.
• Updating Ages: Since the formula uses the TODAY() function, the calculated ages will automatically update every day when you open your Excel workbook, reflecting the current age based on today's date.

Embark on your journey in MS Excel with our guide to the best colleges, innovative programs, online courses, and career opportunities!

Calculate Age in Excel Using the DATEDIF Function 

Follow these simple steps to calculate age from date of birth in Excel using the DATEDIF function. This method accurately calculates age in whole years, considering leap years and the actual number of days each month.

Step 1 - Select the cell where you want the calculated age to appear (in our case, D2).

Step 2 - Type: =DATEDIF(B2, TODAY(), "Y") 

Here- 

• B2: Birthdate cell reference.
• TODAY(): To insert the current date dynamically.
• Y = Number of Years (M = Number of months; D = Number of days)

Step 3 - Press Enter.

Step 4 - Click the cell with the initial formula (e.g., D2) and drag down the fill handle (small square at the bottom right corner) to extend the formula.

Calculate Age in Excel Using the ROUNDDOWN Function 

Calculating age in Excel using the ROUNDDOWN function is another effective method, particularly when you want to display the age in whole years without rounding up. This approach can be beneficial when you need the age calculation to strictly reflect the number of complete years, skipping any additional months or days past the last birthday. Here's how you can do it -

Step 1 - Choose the cell where you want the age to appear (e.g., cell D2).

Step 2 - Type the following formula into the selected cell:

=ROUNDDOWN((TODAY() - Date of Birth)/365.25,0)

=ROUNDDOWN((TODAY() - B2)/365.25,0)

Where:

• TODAY(): Retrieves the current date.
• B2: References the cell containing the birthdate.
• /365.25: Divides the difference by the average number of days in a year, accounting for leap years.
• ROUNDDOWN: Rounds the result down to the nearest whole number, representing age in years.
• ,0: Specifies zero decimal places.

Step 3 - Press Enter to execute the formula and display the calculated age.

Step 4 - If you have multiple birthdates, copy the formula to other cells by dragging the fill handle down to fill the formula into other cells. Excel will automatically adjust cell references for each row, calculating ages for corresponding birthdates.

Calculate Age in Excel Using the INT Function 

You can also use the INT function to calculate age in Excel. Just replace ROUNDDOWN with INT.

Step 1 - Choose the cell where you want the age to appear (e.g., cell D2).

Step 2 - Type the following formula into the selected cell:

=INT((TODAY() - Date of Birth)/365.25,0)

=INT((TODAY() - B2)/365.25,0)

Step 3 - Press Enter to display the calculated age.

Step 4 - For multiple birthdates, drag and drop the formula across the column to fill in other cells.

Key Takeaways

• Versatile Functions for Age Calculation: Excel supports various functions like YEARFRAC, DATEDIF, ROUNDDOWN, and INT to calculate age accurately, catering to different needs and preferences for precision and rounding.
• Dynamic Age Updates: Utilizing the TODAY() function in formulas ensures that age calculations are always current, automatically updating whenever the workbook is opened.
• Leap Year Consideration: Formulas such as =INT(YEARFRAC(B2, TODAY(), 1)) account for leap years and the actual number of days in each month, ensuring precise age calculations.
• Flexibility in Date Analysis: Beyond calculating current age, Excel allows for age calculation on any given date, past or future, offering flexibility for various analytical purposes.
• Ease of Application: Calculating age in Excel is straightforward, from ensuring correct date formats to extending formulas across multiple cells for efficient data processing.