Ageing Formula In Excel: Know All Formulae With Examples

Excel’s ability to calculate age is an incredibly useful tool for managing data in fields like demographics, healthcare, and finance. Whether you need to track employee ages, patient demographics, or customer profiles, knowing how to calculate age efficiently can improve both data accuracy and workflows. In Excel, age calculations are achieved using various built-in functions that calculate the time elapsed between a birthdate and current date.

This article explores different ageing formula in Excel, including DATEDIF, YEARFRAC, ROUNDDOWN and INT, and provides practical examples to enhance your proficiency in Excel.

How to Calculate Age in Excel?

Age calculation typically involves determining the difference between two dates: a birthdate and the current date. Below are some essential ageing formula in Excel functions that are commonly used:

Using the YEARFRAC Function

The YEARFRAC function calculates the difference between two dates as a decimal number.

Syntax and Usage

The syntax for YEARFRAC is: =YEARFRAC(start_date, end_date)

• start_date: The person's birthdate.
• end_date: The current date (or another reference date).

For Example

If someone was born on January 1, 1998, and today’s date is July 1, 2023, the formula =YEARFRAC("1998-01-01", "2023-07-01") will return approximately 25.5 years.

Using the DATEDIF Function

DATEDIF calculates the difference between two dates and is especially useful for age calculations. This formula can be used to calculate age from birthdate in Excel.

The syntax for DATEIF is: =DATEDIF(start_date, end_date, unit)

• start_date: Birthdate.
• end_date: The current date or any other reference date.
• unit: Specifies the type of result:

• • "Y": Complete years.

  • "M": Complete months.

  • "D": Total days.

For Example

To calculate age in years using DATEIF, use the formula =DATEDIF(A1, TODAY(), "Y") where A1 contains the birthdate. This will return the person's age in full years.

Note: The TODAY function in Excel is used to return the current date. It automatically updates whenever the worksheet is recalculated, making it especially useful in dynamic date calculations, such as calculating age, tracking deadlines, or monitoring timelines. Syntax for this is: =TODAY()

Using the ROUNDDOWN Function

The ROUNDDOWN function rounds a number down to a specified number of decimal places. This can be useful when calculating age and ensuring the result is a whole number.

For age calculations, you can combine the DATEDIF function with ROUNDDOWN to eliminate any decimal values.

For Example

=ROUNDDOWN(DATEDIF(A1, TODAY(), "Y"), 0) This formula returns the complete number of years lived without any decimals.

Using the INT Function for Age Calculation

The INT function rounds numbers down to the nearest whole number, which is ideal for calculating age in years.

=INT(YEAR(TODAY()) - YEAR(A1)) This formula calculates the number of full years between a birthdate (in A1) and the current year.

For Example

If you subtract a person’s birth year from the current year, INT can be used to remove any fractional parts.

Summing Up

• DATEDIF and YEARFRAC are the primary functions for calculating age in Excel.
• ROUNDDOWN and INT ensure the results are whole numbers when needed.
• Using these formulas together provides flexibility, whether you're calculating exact or approximate ages.

By mastering these functions, you can enhance your data management and reporting abilities in Excel, making age calculations more efficient and accurate.

Ageing Formula In Excel: Frequently Asked Questions

Q1. How can I calculate age in Excel?

To calculate age in Excel, you can use functions like DATEDIF or YEARFRAC. These functions allow you to determine the difference between a person's birth date and the current date.

Q2. What is the YEARFRAC function?

The YEARFRAC function calculates the number of years between two dates. It returns a decimal value, which represents the fraction of the year that has passed since the last birthday.

Q3. How does the DATEDIF function work?

The DATEDIF function computes the difference between two dates in specified units, such as years, months, or days. It is particularly useful for calculating age by using "Y" for years.

Q4. What is the purpose of the ROUNDDOWN function?

The ROUNDDOWN function in Excel rounds a number down towards zero. When used with age calculations, it provides a whole number by eliminating any decimal values from age results.

Q5. Can I use the INT function to calculate age?

Yes, the INT function can be used to calculate age by converting a fractional year result into an integer. This effectively gives the whole number of years lived.

Suggested Reads: 

• How to Remove Blank Rows in Excel: 5 Effective Methods
• How To Unhide Columns In Excel: Explore Basic & Advanced Methods
• How To Find And Remove Duplicates In Excel (Step-by-Step Guide)
• Subtraction Formula In Excel - Learn Different Ways Using Examples