How to Calculate Age in Excel: 5 Best Methods Explained

by | Power BI

Looking to calculate age in Excel? Well, you’re in the right place.

Whether you need to find the age of a customer, an employee, or any other individual, Excel provides an efficient way to calculate age efficiently.

To calculate age in Excel, you can use the DATEDIF function, which calculates the difference between two dates. The syntax is =DATEDIF(birth_date, TODAY(), “Y”), where birth_date is the cell containing the person’s birth date. This formula calculates the difference in years between the birth date and today’s date, effectively giving the current age.

In this article, we’ll delve into the 5 best methods you can use to calculate age in Excel, and you’ll also learn how to calculate age from a specific date, the current date, or from two different dates.

Let’s rock ‘n’ roll!

How to Calculate Age in Excel

5 Best Methods to Calculate Age in Excel

In this section, we will go over the 5 top methods to calculate age in Excel.

The 5 methods are:

  1. Simple Year Difference

  2. Using DATEDIF Function

  3. Using INT and YEARFRAC Functions

  4. Combining DATE, YEAR, MONTH, and DAY Functions

  5. Calculating Age in Years, Months, and Days

5 Methods to Calculate Age in Excel

Method 1: How to Calculate Age Using Simple Year Difference

To calculate age using the Simple Year Difference method in Excel, subtract the birth year from the current year.

This method gives a quick estimate but doesn’t consider whether the person’s birthday has already occurred this year.

The age formula looks like this:

=YEAR(TODAY()) - YEAR(birth_date)

Here, TODAY() returns the current date and YEAR() extracts the year part of the date.

Suppose we have the following Excel data:

Dataset under analysis

To calculate age for the first person, you can use the following formula:

=YEAR(TODAY()) - YEAR(B2)

In Excel, the age calculation formula looks like the following:

Writing formula for calculating age

Press Enter, and Excel will find the current age of the first person. Use the fill handle to apply the formula to the rest of the column as well.

The final output will be:

Age calculated

Method 2: How to Calculate Age Using DATEDIF Function

The DATEDIF function in Excel is specifically designed for calculating the difference between two dates in years, months, or days.

To calculate age accurately, considering whether the person’s birthday has already occurred this year, you can use the DATEDIF function with the “Y” unit, which represents years.

Its syntax is:

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

To calculate the age of the first person in our case, we will use the following formula:

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

In Excel, the formula looks like the following:

Writing formula for calculating age

Press Enter, and Excel will display the age. Use the fill handle to apply the formula to the rest of the column.

When the formula is applied to the entire column, you’ll see the age of all persons in the dataset.

Age calculated

Method 3: How to Calculate Age Using INT and YEARFRAC Functions

The INT and YEARFRAC functions combined take into account the exact number of years and partial years between two dates.

You can use this method when you need to calculate age in years as a whole number but still consider the fraction of the year that has passed since the last birthday.

The YEARFRAC date function calculates the fraction of a year between two dates, which is perfect for age calculations.

The syntax is:

=YEARFRAC(start_date, end_date, [basis])

[basis] is optional and determines the day count basis to use; if omitted, Excel defaults to a 30/360 basis (basis=0).

The INT function then rounds this fractional year down to the nearest whole number, representing the person’s age in complete years. Combined, these two functions can be written like the following:

=INT(YEARFRAC(start_date, TODAY()))

To calculate the age of the first person in our case, we will use the following formula:

=INT(YEARFRAC(B2, TODAY()))

In Excel, the formula looks like the following:

Writing formula for calculating age

Press Enter and the YEARFRAC function will display the age. Use the fill handle to apply the formula to the rest of the column.

When done, Excel will display the ages in a separate column:

Ages Calculated

Method 4: How to Calculate Age by Combining DATE, YEAR, MONTH, and DAY Functions

This method for calculating age in Excel involves using a combination of the DATE, YEAR, MONTH, and DAY functions to determine the age by considering if the current date is before or after the birthday in the current year.

This approach ensures precise age calculation by accounting for the exact day and month of the birth date in relation to today’s date.

The formula is:

=YEAR(TODAY()) - YEAR(birth_date) - IF(DATE(YEAR(TODAY()), MONTH(birth_date), DAY(birth_date)) > TODAY(), 1, 0)

The components of the formula are:

  • YEAR(TODAY()) retrieves the current year.

  • YEAR(birth_date) extracts the year from the birth date.

  • MONTH(birth_date) and DAY(birth_date) extract the month and day from the birth date, respectively.

  • DATE(YEAR(TODAY()), MONTH(birth_date), DAY(birth_date)) reconstructs the birthday in the current year.

  • IF(… > TODAY(), 1, 0) checks if this year’s birthday has passed. If not, it subtracts 1 from the age to adjust for the upcoming birthday.

To calculate the age of the first person in our case, we will use the following formula:

=YEAR(TODAY()) - YEAR(B2) - IF(DATE(YEAR(TODAY()), MONTH(B2), DAY(B2)) > TODAY(), 1, 0)

In Excel, the formula looks like the following:

Writing formula for calculating age

Press Enter and Excel will display the age. Use the fill handle to apply the formula to the rest of the column.

The output will be:

Ages calculated

Method 5: How to Calculate Age in Years, Months, and Days

Calculating age in years, months, and days in Excel requires a combination of the DATEDIF function to separately calculate the years, months, and days component of age.

This approach provides a detailed age breakdown, offering a complete view of exactly how old someone or something is from a specific date to the current date.

Years Calculation

You can use DATEDIF with the “Y” unit to calculate the complete years between the birth date and today.

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

Months Calculation

After calculating years, use DATEDIF with the “YM” unit to calculate the remaining months beyond the complete years.

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

Days Calculation

Finally, calculate the days beyond the last complete month using DATEDIF with the “MD” unit.

=DATEDIF(birth_date, TODAY(), "MD")

Combining Calculations for Full Age Breakdown

To combine these into a single formula that outputs age as “X years, Y months, Z days”, you can use the following approach:

=DATEDIF(birth_date, TODAY(), "Y") & " years, " & DATEDIF(birth_date, TODAY(), "YM") & " months, " & DATEDIF(birth_date, TODAY(), "MD") & " days"

In our case, the formula will be:

=DATEDIF(B2, TODAY(), "Y") & " years, " & DATEDIF(B2, TODAY(), "YM") & " months, " & DATEDIF(B2, TODAY(), "MD") & " days"

In Excel, the formula looks like the following:

Writing combined formula

Press Enter, and Excel will display the age in years, months, and days. Use the fill handle to apply the formula to the rest of the column.

The final output will be:

Ages calculated

Final Thoughts

Mastering how to calculate age in Excel is more than just a nifty trick; it’s one of the crucial Excel skills that opens up a world of possibilities for analyzing and understanding data.

Whether you’re managing employee records, tracking customer demographics, or planning events, knowing the precise age of individuals involved helps you make informed decisions.

Furthermore, learning these techniques is not just about dealing with numbers. It’s about gaining insights into your data, spotting trends, and tailoring your services or communications more effectively.

Hopefully, you now feel confident with any age calculation you have to do

Did you know that you can use ChatGPT to help with this type of calculation?

Check out our latest vide:

Frequently Asked Questions

In this section, you will find some frequently asked questions you may have when calculating age in Excel.

A team having a meeting in office

How to calculate age from date of birth in Excel?

To calculate age from a date format of birth in Excel, you can use the DATEDIF function. The syntax is:

=DATEDIF(B1, TODAY(), “Y”)

Where B1 is the cell reference containing the birthdate. This formula will return the age in years.

What is the formula for age calculation in Excel based on a specific date?

To calculate age in Excel based on a specific date, you can use the DATEDIF function with the specific date as the end_date argument.

The syntax is:

=DATEDIF(B1, C1, “Y”)

Where B1 is the cell reference containing the birthdate, and C1 is the cell reference containing the specific date. This formula will return the age in years either in whole numbers or decimal value based on the specific date.

How can I calculate age in Excel using the YEAR function?

To calculate age in Excel using the YEAR function, you can subtract the birth year from the current year.

The syntax is:

=YEAR(TODAY())-YEAR(B1)

Where B1 is the cell reference containing the birthdate. This formula will return the age in years.

Related Posts

Using the DISTINCT Function Effectively in DAX

DAX Table Functions Deep Dive

Explore an in-depth analysis of DAX table functions in Power BI, comparing SUMMARIZE and ADDCOLUMNS, and understanding INTERSECT and EXCEPT for enhanced data manipulation and analysis.