Excel Formula to Calculate Age on a Specific Date in Years and Months

Calculating age on a specific date is a common requirement in many Excel worksheets. Whether you are managing employee data, customer information, or personal finances, knowing the age of a person on a specific date can provide valuable insights.

In this article, we’ll show you how to use Excel formulas to calculate age on a specific date in years and months. We’ll cover two different methods: one for calculating age in years and months separately, and another for calculating age in a combined format.

Method 1: Calculating Age in Years and Months Separately

The first method we’ll cover involves calculating age in years and months separately. This method is useful if you need to report age in two separate columns. Here’s how to do it:

Step 1: Enter the Birthdate

Enter the birthdate of the person you want to calculate the age for in a cell. Let’s assume the birthdate is in cell A2.

Step 2: Enter the Date to Calculate Age for

Enter the date you want to calculate the age for in another cell. Let’s assume the date is in cell B2.

Step 3: Calculate the Age in Years

To calculate the age in years, use the following formula:

=DATEDIF(A2,B2,”Y”)

This formula calculates the number of complete years between the two dates. It uses the DATEDIF function, which is a hidden function in Excel that calculates the difference between two dates in different units.

Step 4: Calculate the Age in Months

To calculate the age in months, use the following formula:

=DATEDIF(A2,B2,”YM”)

This formula calculates the number of months between the two dates, excluding the years. It uses the same DATEDIF function as the previous formula, but with the “YM” argument.

Step 5: Display the Age in Years and Months

To display the age in years and months, concatenate the results of the previous two formulas using the “&” operator. Here’s the formula:

=DATEDIF(A2,B2,”Y”)&” years “&DATEDIF(A2,B2,”YM”)&” months”

This formula displays the age in the format “x years y months”, where x is the number of complete years and y is the number of remaining months.

Method 2: Calculating Age in a Combined Format

The second method we’ll cover involves calculating age in a combined format. This method is useful if you need to report age in a single column, with both years and months combined. Here’s how to do it:

Step 1: Enter the Birthdate

Enter the birthdate of the person you want to calculate the age for in a cell. Let’s assume the birthdate is in cell A2.

Step 2: Enter the Date to Calculate Age for

Enter the date you want to calculate the age for in another cell. Let’s assume the date is in cell B2.

Step 3: Calculate the Age in Months

To calculate the age in months, use the following formula:

=DATEDIF(A2,B2,”M”)

This formula calculates the number of complete months between the two dates.

Step 4: Calculate the Age in Years

To calculate the age in years, divide the result of the previous formula by 12, using the INT function to round down to the nearest integer. Here’s the formula:

=INT(DATEDIF(A2,B2,”M”)/12)&” years “&MOD(DATEDIF(A2,B2,”M”),12)&” months”

This formula first divides the number of months by 12

What is the Excel formula to calculate age on a specific date in years and months?
The formula to calculate age on a specific date in years and months is: =DATEDIF(date_of_birth, specific_date, “y”) & ” years, ” & DATEDIF(date_of_birth, specific_date, “ym”) & ” months”

What do I need to enter in the formula?
You need to enter the date of birth of the person in the cell next to “date_of_birth” and the specific date you want to calculate the age for in the cell next to “specific_date”.

Can I use this formula for multiple people?
Yes, you can use this formula for multiple people by copying the formula to other cells and changing the values of “date_of_birth” and “specific_date” for each person.

What if I want to calculate the age in months only?
To calculate the age in months only, you can use the formula: =DATEDIF(date_of_birth, specific_date, “m”)

Can I format the result to show only years and months?
Yes, you can format the result to show only years and months by selecting the cell containing the formula, right-clicking, and choosing “Format Cells”. In the “Number” tab, select “Custom” and enter the format: “0 years, 0 months”.

Can I use this formula for future dates?
Yes, you can use this formula for future dates. The result will be negative, indicating the number of years and months left until the specific date.

What if the result shows #NUM! error?
If the result shows #NUM! error, it means that the specific date is earlier than the date of birth. Make sure you have entered the dates correctly and switch the values of “date_of_birth” and “specific_date” in the formula.

What if the result shows #VALUE! error?
If the result shows #VALUE! error, it means that one of the dates is not recognized as a valid date. Check that the dates are entered in the correct format (dd/mm/yyyy) and try again.

Can I use this formula for calculating the age of animals or objects?
Yes, you can use this formula for calculating the age of animals or objects, as long as you have their date of birth or creation and the specific date you want to calculate the age for.

Is there any other Excel function to calculate age?
Yes, you can also use the function YEARFRAC to calculate the age in years and fractions of years. However, it does not provide the age in months. The formula is: =YEARFRAC(date_of_birth, specific_date)