How To Calculate Age in Excel [12 Easy Examples]

Calculating ages can be a tedious affair if you’re doing it manually. Luckily, Excel provides several functions and techniques to calculate age based on a person’s date of birth using the current date or a specific date.

Using these tools, you can generate precise age calculations with ease. In this guide, we will explore different methods to calculate age in Excel, accommodating both the current date and specific reference dates. Read on to learn more.

How To Calculate Age in Excel

Here’s how Excel can calculate DOB to age:

1. In a cell, type in the “equals” sign (=) and the “DATEDIF” function.
2. Select the cell with the date of birth.
3. Add a comma and write the “TODAY” function.
4. Add another comma and “y” in quotation marks to return the results in years.
5. Press “Enter” and drag the formula down to copy it.

How Do You Calculate Age in Excel? (Which Functions)

There are a couple of different functions you can use in Excel to calculate age, just like in Google Sheets. The most commonly used function is the “DATEDIF” function. However, you can also use other functions like “YEARFRAC” and “ROUNDDOWN.”

We’ll cover all of the ways Excel can convert data to age. You can take your pick from the table of contents below!

Related: Google Sheets vs. Microsoft Excel

How To Calculate Age from Date of Birth in Excel in Years

You can calculate a person’s age in Excel from their date of birth to their current date. To do this, you’ll need to use the “DATEDIF” function along with the “TODAY” function or a specific date.

The “DATEDIF” function in Excel calculates the difference between two dates in various units, such as years, months, or days.

The syntax of the “DATEDIF” function is as follows:

=DATEDIF(start_date, end_date, unit)

• start_date: The starting date or the earlier date in the calculation.
• end_date: The ending date or the later date in the calculation.
• unit: The unit of measurement to calculate the difference. It can be one of the following options:
  • “y” – Calculates the difference in complete years.
  • “m” – Calculates the difference in complete months.
  • “d” – Calculates the difference in days.
  • “ym” – Calculates the difference in months, excluding years.
  • “md” – Calculates the difference in days, excluding years and months.

For this example of how to calculate current age in Excel, we’ll be using the worksheet below:

Here’s a step-by-step guide for how to convert DOB to age in Excel:

1. In a cell, type in the “equals” sign (=) and the “DATEDIF” function.

2. Select the cell with the date of birth.

3. Add a comma and write the “TODAY” function.

4. Add another comma and “y” to return the results in years.

5. Press “Enter” to calculate age in Excel from a birthday. Click and drag the formula if you want to apply it to other cells.
   

   

The complete formula will be:

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

This formula calculates the difference in years between the date of birth (cell B2) and the current date.

How To Calculate Age from Date of Birth in Months

You can calculate a person’s age in Excel from their date of birth to the current date in months. To do this, you’ll need to use the “DATEDIF” function along with the “TODAY” function or a specific date.

For this example of how to calculate age from DOB in Excel, we’ll be using the worksheet below:

Here’s a step-by-step guide for how to convert DOB to age in Excel:

1. In a cell, type in the “equals” sign (=) and the “DATEDIF” function.

2. Select the cell with the date of birth.

3. Add a comma and write the “TODAY” function.

4. Add another comma and “m” to return the results in years.

5. Press “Enter” and drag the formula down to copy it.

The complete formula will be:

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

To get the age in days, you can follow the same procedure and replace the “m” with “d” in the formula.

How To Calculate Age Between Two Dates

You can also use the “DATEDIF” function to calculate the age between two dates in Excel. This function allows you to determine the difference in years, months, or days between two dates.

We’ll use the following example to calculate the age between two dates:

The syntax for this formula is:

=DATEDIF(start_date, end_date, "y")

Here’s a step-by-step guide on how to calculate the age between two dates in Excel:

1. In a cell, type in the equal sign and the “DATEDIF” function.

2. Select the cell with the start date.

3. Add a comma and select the cell with the end date.

4. Add another comma and “y” to return the results in years.

5. Press “Enter” and drag the formula down to copy it.

The complete formula will be:

=DATEDIF(A2,B2, "y")

To calculate age more precisely, you can include months or days in the formula. To include months in the calculation, change the last argument of the formula to “ym.”

The formula would be:

=DATEDIF(start_date, end_date, "ym")

To include days in the calculation, change the last argument of the formula to “md.”

The formula would be:

=DATEDIF(start_date, end_date, "md")

How To Calculate Age on a Specific Date

You can also use a specific date instead of using today or the current date. For example, in our worksheet, instead of working with the current date, we can use the specific date, 7/20/22.

Here’s how to calculate age from DOB in Excel to a specific date:

1. In a cell, type in the equal sign and the “DATEDIF” function.

2. Select the cell with the date of birth.

3. Add a comma and select the cell with the specific date.

4. Press F4 to lock the second cell reference if you do not want to change the reference.

5. Add another comma and “y” in quotation marks to return the results in years.

6. Press “Enter” and drag the formula down to copy it.

The formula for this will be:

=DATEDIF(B2, $B$11, "y")

This formula calculates the difference in years between the date of birth in cell B2 and the specific date in cell B11 using the “DATEDIF” function.

The “DATEDIF” function returns the age as a whole number of years. If you need a more precise age calculation, you can modify the formula to include months and days by changing the units argument.

How To Get Age from Birthday in Years, Months, and Days.

If you want to get the age in years, months, and days, it can get a little complicated. You can use a combination of functions, such as “DATEDIF,” “YEAR,” “MONTH,” “DAY,” and “TODAY.”

You’ll need to combine three formulas for each of the formats.

Here’s a step-by-step guide for how to use the formula to calculate age in years, months, and days:

1. In a cell, type in the “equals” (=) sign and the “DATEDIF” function.

2. Select the cell with the DOB.

3. Now we start with the first formula for the year. Add the formula “TODAY(),”Y”)”

4. Add the word & ”years” in quotations to separate the years from the rest.

5. Next, we can add the second formula for the months. Add “DATEDIF(B2,TODAY()”

6. Add the word “months” in quotations to separate the years from the rest.

7. Finally, the last formula for the days is:

   DATEDIF(B2,TODAY(),"MD")

8. Add the word “days” in quotation marks.

9. Close the brackets and press “Enter.”

10. Copy the formula to the rest of the cells.

The age will be calculated and displayed in cell B2, showing the years, months, and days.

With this formula, you can calculate the age from a birthday and display it in years, months, and days in Excel. It allows for a more detailed representation of the age, providing a comprehensive understanding of the time elapsed since the date of birth.

How To Calculate When a Person Will Reach a Certain Age

You can also find out the exact date a person will turn a specific age in Excel using the “DATE” function if you have their date of birth.

For this example, we’ll be using the following worksheet. In our example sheet, we can determine the date the people will be 25 years old.

To do this, we’ll use the “DATE” and “YEAR” functions. The date function will help us find the exact date, while the “YEAR” function will make sure that we are working with years in the formula.

Here’s how to calculate when a person will reach a certain age in Excel:

1. In the cell, enter the equal sign and the “DATE” function.

2. After the bracket, add the “YEAR” function.

3. Select the cell with the date of birth and close the brackets.

4. Add “+25” or the age that you want to use.

5. Add a comma, then add the “MONTH” function.
6. In the brackets, select the cell with the date of birth
7. Add a “DAY” function, then select cell B2 again.

8. Close the brackets and press “Enter.”
9. Copy the formula to the rest of the columns.

The complete formula is as follows:

=DATE(YEAR(B2)+25, MONTH(B2), DAY(B2))

How To Create an Age Calculator in Excel

In a few simple steps, you can also automate calculating age in Excel. Creating an age calculator in Excel allows you to quickly calculate the age based on a person’s date of birth.

Here’s a step-by-step guide on how to create an age calculator in Excel:

1. Create a cell for the date of birth.

2. Create another section for the current age.
3. In the cell for the current age, add the formula:

   =DATEDIF($B$1,TODAY(),"y")

If you want to calculate a person’s age, all you need to do now is put their date of birth in cell B1.

This lets you quickly find the age of a person with just their date of birth. Automating this function will make your workflow smoother and faster.

Related: The Best Excel Alternatives for macOS

How To Calculate Age in Excel Without DATEDIF

If you prefer to calculate age in Excel without using the “DATEDIF” function, you can achieve the same result using other built-in functions.

Some of the alternative functions include:

1. “YEARFRAC”
2. “SUBTRACT”
3. “ROUNDDOWN”
4. “YEAR”

These functions all work in different ways, and some of them need to be customized to be more accurate. Below, we’ll show you how to use each function with an example.

How To Calculate Age Using the YEARFRAC Function

The “YEARFRAC” function is one of the most straightforward functions you can use for calculating age in Excel.

The syntax for “YEARFRAC” is:

=YEARFRAC(Birth_Date,Specific_Date) 

However, this formula usually returns the age with decimal points. In order to return a whole number instead, we add the function “INT.”

The complete formula is:

=INT(YEARFRAC(Birth_Date, Specific_Date))

Here’s how to calculate age in Excel using the “YEARFRAC” fraction:

1. In a cell, type in the “equals” (=) sign and “INT” function.

2. Add the “YEARFRAC” function.

3. Select the cell with the date of birth.

4. Add a comma and write the “TODAY” function or select the cell with the specific date.

5. Close the brackets.
6. Press “Enter” and drag the formula down to copy it.

How To Calculate Age in Excel by Subtracting

You can also easily get the age in Excel by subtracting the date of birth from the end date. This is a simple way, but it usually returns the age in days, so we’ll need to customize the formula to return the age in years. You can do this by adding “/365” to the formula to divide the days into years.

Here’s how to calculate age from date of birth by subtracting:

1. In the cell type, the “equals” sign (=) and an opening brackets
2. Add the “TODAY” function.

3. Type the “minus” sign (-).
4. Select the cell with the date of birth.

5. Close the brackets and add “/365.”

This gives you the age of the person on the current date.

If you want to use a specific date other than the current date, then here’s what to do:

1. In the cell type, the “equals” (=) sign and an opening bracket.
2. Select the specific date. Remember to lock the cell by clicking the F4 button.
3. Type the “minus” (-) sign.
4. Select the cell with the date of birth.

5. Close the brackets and add “/365.”

Both these formulas will give you decimal points. If you’d like to have whole numbers, instead, you can select the cells and format them to remove the decimal points.

In the home tab, under numbers, you can click the decrease decimal points option to remove them.

How To Calculate Age Using the ROUNDDOWN Function

Another function you can use is the “ROUNDDOWN” function. The function is mostly used to remove the decimal points in formulas. In this case, all we’ll be doing is subtracting the specific date and the date of birth.

The syntax for the “ROUNDDOWN” function is:

=ROUNDDOWN((Specific_Date - Birth_Date)/365,0)  

1. In a cell, type in the “equals” sign (=) and “ROUNDDOWN” function.

2. Select the cell with the specific date and add a “minus” sign (-). Remember to lock the specific date cell reference by clicking F4.

3. Select the cell with the date of birth.

4. Close the brackets. And add “/365” to get the age in years.

5. Press “Enter” and drag the formula down to copy it.

This formula gets the days between the two dates and divides it by 365 to give you the number of years in between.

How To Calculate Age Using the YEAR Function

Finally, you can use the “YEAR” function to get the age in Excel. This function also works by subtracting the date of birth from the end date, like the previous function. The only difference is that with this function, the returns are automatically in years, so you don’t need to customize the formula.

Here’s how to calculate age based on a date of birth in Excel with the “YEAR” function.

1. In the cell, type the “equals” sign (=) and add opening brackets
2. Type the “YEAR” function and add another opening bracket
3. Type the “TODAY” function and close the brackets
4. Add a “minus” (-).
5. Type “YEAR,” and after the brackets, select the cell with the date of birth.
6. Close the brackets and press “Enter.”

The formula for this is:

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

This formula finds the age in the current date. If you want to use a specific date instead, you can use the cell reference for the date instead of the “TODAY” function.

Wrapping Up

Calculating age in Excel is a fundamental task that can be accomplished using various methods. The most common (and easiest) approach is using the “DATEDIF” function, which allows you to calculate the age based on the difference between a person’s date of birth and the current date.

Additionally, we’ve shown you how to create an age calculator to help you determine age in Excel. This provides a convenient way to automate age calculations and streamline data analysis.

You can learn more about how to use Excel and Excel functions with the best Excel courses.

Alternatively, if you’d like some powerful, ready-to-use spreadsheet templates, consider checking out our premium template library, and remember, you can use the code SSP to save 50%.

Get Premium Templates

Related:

• How To Use “Does Not Equal” in Excel [Easy 10 Min Guide]
• How To Pull Data From Another Sheet in Excel
• How To Combine Rows in Excel
• Guide to Excel: IF Statements with Multiple Conditions
• Can Excel Count Cells with Text? [Easy Guide]