How to Calculate Age in Excel (7 Easy Formulas)

Excel doesn't have a dedicated function for calculating age, but there are several ways we can use someone's date of birth to compute the person's age. We won't just learn how to compute someone's age in years, we'll even boil their age down to months and days.

There are several formulas, as well as combinations of formulas, that we can use to calculate age in excel. We'll talk about each of those formulas one by one. So without further ado, let's jump in.

Recommended Reading: How to Calculate Years of Service in Excel

Calculating Age in Excel With the DATEDIF function

The DATEDIF function does exactly what the name suggests. It calculates the DATE+DIF (where DIF may be inferred as DIFFERENCE) and gives us the output in our preferred unit. For instance, let's say David's DOB is 10/01/1990.

We want to know how old David is today.

So, we'll use the following formula to find out David's age:

The first argument is David's DOB, the second argument is today's date, and "y" tells the formula that we want the output in terms of years. Finally, we have concatenated a text "year(s)" which is displayed as an identifier against the age. So, as on the day of writing (Jun 24, 2021), Excel tells us that David is 30 years old.

Excel subtracted the serial number of the date entered in the first argument from the serial number of the date entered in the second argument to compute the difference in terms of days. Then, it converted it into years and eliminated the decimal value to give us the final output.

In the next row, we change "y" to "m" and the identifier from "year(s)" to "month(s)". This changes the formula so that it now gives us David's age in the number of months, which is 368 months.

To compute the number of months, it does the same thing, except that the days are converted to months instead of years. We can even take this a step further and use "d" in the final argument to compute David's age in the number of days. In this case, we do this in the third row, where our final output comes to 11,210 days.

Age as on a Given Date in the Past

We can also tweak the DATEDIF function slightly and find out David's age as on any specific date. For this, we'll use the DATE function instead of the TODAY function and nest it in the DATEDIF function's end_date argument.

Let's assume that we want to calculate David's age as of Christmas 2018.

We'll use the following formula:

This formula works exactly the same way as the previous one. As you can see, the only difference is in the second argument where we have supplied a specific date instead of using the TODAY function.

The output, of course, is 28 years. This makes sense because the date we've entered is roughly 2.5 years in the past. Since the output ignores the value after the decimal, we get 28 (i.e., 30 – 2) years as our output. In the subsequent rows, we get 338 months as our output when we enter "m" in the final argument and 10,298 days as our output when we enter "d" in the final argument.

Age as on a Given Date in the Future

This exercise has inspired David's curious side. He was hoping to get busy with some financial planning. He has set a few financial goals for himself and wishes to compute his age at a future date to compute his taxability and maturities for investments.

No worries, let's help David figure out his age using the following formula:

This formula will help us compute David's age in terms of years as on Christmas day of the year 2045. There's no change in the formula except the year in the DATE function. Naturally, the formula returns David's age as on the supplied date as 55 years. Changing the final argument to "m" gives us 662 months, and changing the final argument to "d" gives us 20,160 days.

Using YEARFRAC function to Calculate Age in Years

Before we begin working on this, note that DATDIF is generally a more preferable way to compute age. It's possible to compute age using the YEARFRAC function as well, but it allows us to compute the age only in terms of years.

To compute David's age using the YEARFRAC function, we'll use the following formula:

Let's start with why we have the INT function in the formula. The INT function has a sole purpose – removing values after the decimal. When the YEARFRAC function returns, for example, 10.3 years and relays it to the INT function, it will convert it to 10 years.

Moving on, let's see what the YEAFRAC function does. The first argument in the function is the start_date argument and the second argument is the end_date argument. It's similar to what we saw with the DATEDIF function, except that there is no third argument and the YEARFRAC function's output does contain a decimal value.

In our example, the YEARFRAC function returns 30.69 years. This output is relayed to the INT function, which gives us our final output of 30 years.

Recommended Reading: How to Add Years to a Date in Excel

Find out Age in Years, Months, and Days

The previous formulas helped David get his age in terms of years, months, and days. However, what formula must he use to compute his exact age? For instance, in this format – 20 years, 6 months, 15 days.

We know that when we want to connect outputs of two formulas, we use a concatenation (&). Therefore, we'll use the following formula:

So, why use "ym" and "md" instead of "m" and "d" like we did before? Well, "ym" instructs the formula to compute the difference only between the months entered in the first and second argument. Similarly, "md" instructs the formula to compute the difference only in the days entered in the first and second argument.

In this case, these are the differences we want. Think about it—using "m" and "d" would have given us the total months/days between David's DOB and today—and that's not what we want.

Our output, therefore, is "30 year(s) 8 month(s) 9 day(s) ". We can format the text strings in the formula to add commas or spaces as required.

Calculating When a Person Will Turn X Years Old

David's curiosity is driving him nuts. Lucky for us, he wants to learn everything there is about calculating date differences in Excel. He now wants to figure out when he will turn 35 years, 45 years, and 55 years so he can plan his life accordingly.

More power to him, I say. Let's help him with our next formula:

This is a pretty straightforward formula where we have used the DATE function to add 35 years to David's DOB. The DATE argument picks up the year, month, and day component and compiles them into a valid Excel date.

In fact, if David's date of birth were split into three different cells, each containing a different component of his DOB (i.e., year, month, and day), this formula would still work. We'd just need to reference the relevant cells instead of cell A2.

When the formula picks up the year component, we simply add 35 to it, which gives us our final output. In our example, David turns 35 on October 15, 2025, turns 45 on October 15, 2035, and turns 55 on October 15, 2045.

Finding Age Difference Between Two Individuals

David is unstoppable but promises this will be his last exercise for computing ages. We are onboard. This time, David wants to see how he can compute the difference between the ages of two individuals. He has a list of DOBs he wants to compare his own age with.

To accomplish this, we'll use the following formula:

We know that any time we want to compute the difference in dates, we default to the DATEDIF function.

However, there is one small problem. When David wants to compare his age with someone younger than him, the DATEDIF function's cell references will need to change. The date in the start_date argument must necessarily occur before the value in the end_date argument, otherwise, the function will return a #NUM error (like we can see in the C5 cell).

It can be fixed by referencing cell B2 in the first argument and A2 in the second argument. However, if you're working with a large dataset, you may need a more convenient alternative; a formula you can drag down across the column.

To remedy this, let's just use two very simple functions and plain algebra, like so:

Let's start from what's inside the inner-most brackets. The (A2-B2) is a subtraction of two dates. Excel will subtract the serial numbers of each date and the output will be the difference between these dates in terms of days. Next, we just divide it by 365 to convert it to years.

Then, we have the ABS function. The ABS function ensures that we always have a positive value. For instance, in the second row, the date in B3 falls after the date in A3, and that gives us a negative number. The ABS function will convert this value into a positive number and relay it to the INT function.

INT function, as we discussed before, gives us the integer value (i.e., removes the value after the decimal). This gives us a nice, clean output of 4 years, 4 years, and 6 years, respectively.

An important thing to note here is that you must always nest the ABS function inside the INT function and not vice versa. This is because the INT function rounds positive numbers towards zero, and negative numbers away from 0. For instance, in the second row, if we nest the INT function inside the ABS function, our output changes to 5.

This concludes our age calculating spree with David. We used a range of functions that can help us compute a person's age, or even figure out when a person will turn x number of years. While you work on mastering these techniques, we'll put together another tutorial for you. Until next time.