How to calculate age in Excel?

MS Excel, or Excel, is widely used spreadsheet software with a wide range of built-in tools and features. It helps us record various data sets and perform calculations across them with multiple cells. An example of a classic calculation is to calculate the difference between two dates. However, it does not sound like a useful calculation. But, the same technique is somewhat crucial to finding or calculating someone or something's age in Microsoft Excel.

Apart from finding the age of any person, we can use the same concept to calculate the duration of any project, the years of existence of any company, the number of years elapsed between the specified dates, etc.

In this tutorial, we discuss different methods or solutions on how to calculate age in Excel. The tutorial helps us learn to calculate ages as numbers of complete years, months, and days at the current date or any specific date.

How can we calculate the age in Excel?

Although there is no specific function in Excel to calculate age, we have many ways to calculate age differently in different scenarios. However, we must know the individual's original date of birth (D.O.B.). We then supply the date of birth in conjunction with the Excel functions, specifically DATEDIF and TODAY, to calculate the age or differentiate between dates. Let us now discuss the common scenarios of finding the age:

Calculating Age in Years

We usually consider several factors while calculating any person's age. For example, we may have to calculate age in years, months, days, or all of these together. However, calculating a person's age in years is Excel's most commonly used task.

Assuming that we know the date of birth, a few different functions in Excel can help us calculate a person's age in years. Let us discuss each useful age formula in detail:

Using the DATEDIF Function

The DATEDIF function in Excel is the most common function to calculate a person's age. It is an easy-to-use, built-in, and the most proper function that accepts the date of birth as an input date and returns the person's age as output data.

In another way, the DATEDIF function typically converts the date of birth into the corresponding person's age. The primary advantage of the DATEDIF function is that it can be used to calculate age in various formats, such as years only, months only, days only, or the combined form of years, months and dates, etc.

Unlike the other Excel functions, the DATEDIF function does not appear in the quick list of functions. That means we don't see a DATEDIF function as the suggestion when we start typing it within an Excel cell after an equal sign. However, the function works in all versions of Excel. We must know the syntax and required arguments to use the DATEDIF function in Excel.

The generic syntax of the DATEDIF function is defined as below:

As shown here, the function requires the following three arguments:

• start_date: This argument is used to specify the initial/starting date of the period for which we want to calculate the difference result. It can be given as dates, text strings within double quotes, serial numbers, or as the result of other functions such as DATE().
• end_date: This argument is used to specify the ending date of the period for which we want to calculate the difference result. It accepts data similar to the start_date.
• unit: This argument helps us to specify the type of result we need to retrieve by DATEDIF. We can get six different output formats depending on the unit type we provide. We can enter units Y, M, D, MD, YM, and YD. Here, Y stands for Years, M for Months, and D for Days.

It is important to note that Y, M, and D return the numbers in whole years, months, and days, respectively. In contrast, YM only returns the date difference in months while ignoring the corresponding days and years, MD only returns the date difference in days while ignoring the associated months and years, and YD returns the date difference in days while ignoring the corresponding years.

When calculating the age in years using the DATEDIF function, the more familiar syntax can be defined as:

=DATEDIF(Birth_Date,Specific_Date,"Y")

To calculate the age from the date of birth to till date, we can specify the current date in the place of Specific_Date. Besides, we can also use the TODAY function instead of the current date. Here is the respective formula to calculate the age of a person in years till today's date:

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

Suppose we have a person's birth date in cell B2, and we need to calculate the current age in years. Then, we can use the reference of the birth date in the last formula in the following way:

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

Sometimes, we may see a specific date instead of the age in years. In such a case, we must navigate the Home tab > Number Format drop-down > select 'General' instead of 'Date'.

Using the YEARFRAC Function

Another useful method to calculate the age in Excel involves using the YEARFRAC function. It is an easy-to-use Excel function and is often used to calculate ages in years. It helps us retrieve the age from the given birthdate to a specified date.

The generic syntax to calculate the age of a person using the YEARFRAC function is defined as below:

If we need to calculate the age from birth to today's date, we can specify the current date in place of Specific_Date. Alternatively, we can also join the YEARFRAC function with the TODAY function in the following way:

=YEARFRAC(Birth_Date,TODAY())

By default, the above formula returns the results in decimal numbers. This does not look good when calculating a person's age. So, we combine or enclose the formula within the INT function to return the corresponding age as an integer. Thus, the complete formula to calculate the age in Excel using the YEARFRAC function is defined as below:

=INT(YEARFRAC(Birth_Date,Specific_Date))

Suppose we have the current date in cell A2 and the person's birth date in cell B2. In that case, we can calculate the age of that particular person using the below formula:

=INT(YEARFRAC(B2,A2))

If we use the YEARFRAC function with the TODAY function to calculate age in years, the formula looks like this:

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

When combined with the TODAY function, the YEARFRAC function only returns the current or most recent age in years.

Using the ROUNDDOWN Function

Although rarely used, we can also use the ROUNDDOWN function to calculate age in Excel. The following is the syntax to calculate age in Excel using the ROUNDDOWN formula:

Generally, the ROUNDDOWN function helps to round down the decimal places. However, we have customized the formula in a way that it calculates age in years. In the formula, we use 365.25 for a leap year (366 days in a year) that comes every four years. We use 0 as the last argument in the ROUNDDOWN function to ignore the decimal places in the age.

The ROUNDDOWN formula is a good practice to calculate age, but not suggested as it is not flawless. Suppose a child has not yet lived through any leap year, and we calculate the age with this formula dividing by 365.25; the formula will return the wrong age.

Dividing by the average number of days a year also works fine in most cases, meaning we can divide by 365 days instead of 365.25. However, this case also has some issues and produces wrong results sometimes. For instance, suppose someone's D.O.B. is Feb 29, and the current date is Feb 28. In that case, if we divide by 365, the age retrieved by the formula will be one day older. So, we must divide by 365.25 to calculate the date in this case. Thus, these two approaches are not perfect. Using the DATEDIF function to calculate a person's age in Excel is always recommended.

Suppose we have the current date in cell A2 and someone's D.O.B. in cell B2. In that case, we can calculate the age of that particular person using the below formula:

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

In addition, we can also use the TODAY function in place of the Specific_Date to calculate the person's age to the current date.

Using the TODAY Function

Since the age is most commonly calculated by subtracting the birth date from the current date, the TODAY function in Excel also helps us calculate the age to some extent. Like the ROUNDDOWN formula, the TODAY formula also isn't perfect for calculating someone's age in Excel.

Suppose that we have someone's birth date in cell B2; we can apply the TODAY formula for calculating age in the following way:

=(TODAY()-B2)/365

Based on certain cases, we may sometimes need to divide by 365.25 instead of 365. In this formula, the first part (TODAY()-B2) usually calculates the difference between the current date and the birth date. The second part of the formula helps divide the difference by 365 to get the number of years (i.e., age in years).

Unfortunately, the TODAY formula here provides the results in decimals, just like the YEARFRAC function. Therefore, we enclose the TODAY formula within the INT function to display the age in whole years or the nearest integer value. So, the final TOTAL formula to calculate age in years goes as follows:

=INT((TODAY()-B2)/365)

Calculating Age in Months

As we said earlier, the DATEDIF function can help us calculate someone's age in different formats. So, we can use the same DATEDIF formula again in our worksheet. However, we must change the unit in the formula from 'Y' to 'M'. This tells Excel to display or return age in months.

Let's reconsider the same example data set where we have someone's birth date in cell B2. We need to calculate the current age in months. Then, we can use the reference of the birth date in the DATEDIF formula in the following way:

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

Calculating Age in Days

Calculating age in days becomes easy when we already know the syntax of the DATEDIF function. As in the previous example, we changed the unit argument from 'Y' to 'M', which helped us calculate the person's age in months. Similarly, if we change the unit argument from 'M' to 'D', the function will return the age in days. Thus, considering that if a person's D.O.B. is in cell B2, the formula will be as below:

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

Calculating Age in Years, Months, and Days altogether

As discussed above, calculating a person's age in individual years, months and days is surprisingly easy. However, this may not always be sufficient. There may be cases when we have to find or calculate the exact age of the person in years, months and days altogether. In such cases, the formula becomes a little lengthy but still easy.

To calculate the person's exact age in whole years, months and days, we must use three different DATEDIF functions and combine them in a formula simultaneously. Suppose if the person's birth date is in cell B2, the three different DATEDIF functions will be as follows:

• To calculate numbers of complete years: =DATEDIF(B2,TODAY(),"Y")
• To calculate the numbers of remaining months: =DATEDIF(B2,TODAY(),"YM")
• To calculate the numbers of remaining days: =DATEDIF(B2,TODAY(),"MD")

We now combine all these DATEDIF function using the '&' operator in the following way:

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

Although we get the age in years, months, and dates as a single string, it is not meaningful. To make the results (or age) effective or understandable, we separate each unit using the comma and determine what each value means. So, the formula becomes this:

=DATEDIF(B2,TODAY(),"Y") & " Years, " & DATEDIF(B2,TODAY(),"YM") & " Months, " & DATEDIF(B2,TODAY(),"MD") & " Days"

The above image shows that the age results are comparatively more meaningful than the earlier one. However, it also displays some zero values. We can further improve our DATEDIF formula by combining it within the three different IF statements to check and eliminate zeros. So, the final Excel age formula to calculate someone's current age in years, months, and days become this:

=IF(DATEDIF(B2, TODAY(),"Y")=0,"",DATEDIF(B2, TODAY(),"Y")&" Years, ")& IF(DATEDIF(B2, TODAY(),"YM")=0,"",DATEDIF(B2, TODAY(),"YM")&" Months, ")& IF(DATEDIF(B2, TODAY(),"MD")=0,"",DATEDIF(B2, TODAY(),"MD")&" Days")

In the image above, we only see non-zero values of the person's age. However, the formula only finds the current age of the person.

Calculating Age on any Specific/Particular Date

In the syntax of formulas above, we already discussed the way that tells us to find someone's age on a specific date. For example, the DATEDIF formula below calculates the age of a person on a specific date:

=DATEDIF(Birth_Date,Specific_Date,"Y")

The unit argument can be changed as required. In the above formula, we can typically supply the cell reference for both dates, and the result will appear on the destination cell.

Another typical method to use the same formula to calculate the person's age on any specific date will be to supply the desired date directly in the formula. For instance, suppose we have someone's D.O.B (30/05/1995) in cell B2, and we want to know the person's age on 01/01/2021. So, we can use the DATEDIF function in conjunction with the DATE function in the following way:

=DATEDIF(B2,DATE(2021,1,1),"Y")

In the above image, we use the DATE function to provide the specific date directly in the DATEDIF function, while the B2 reference is used for the D.O.B.

Apart from this, if we need to find the person's date in years, months, and days at a certain date, we can use the same above-discussed DATEDIF concept where we combined three DATEDIF functions. However, we must replace the TODAY() function in the second argument with our desired date.

So, when a person's birth date is in cell B2 and we need to calculate age as of 01/01/2021, we use the below formula:

=IF(DATEDIF(B2, "1/1/2021","Y")=0,"",DATEDIF(B2, "1/1/2021","Y")&" Years, ")& IF(DATEDIF(B2, "1/1/2021","YM")=0,"",DATEDIF(B2, "1/1/2021","YM")&" Months, ")& IF(DATEDIF(B2, "1/1/2021","MD")=0,"",DATEDIF(B2, "1/1/2021","MD")&" Days")

Instead of putting the specific date in the formula, we can use a cell reference for the specific date and make our formula easy to understand. Suppose the person's birth date is in cell B2, and the specific date on which we want to calculate age is in cell C2, then the flexible age formula will be as follows:

=IF(DATEDIF(B2, C2,"Y")=0,"",DATEDIF(B2, C2,"Y")&" Years, ")& IF(DATEDIF(B2, C2,"YM")=0,"",DATEDIF(B2, C2,"YM")&" Months, ")& IF(DATEDIF(B2, C2,"MD")=0,"",DATEDIF(B2, C2,"MD")&" Days")