Age as of formula excel

To calculate the age of a person in Excel, use DATEDIF and TODAY. The DATEDIF function has three arguments.

1. Enter the date of birth into cell A2.

2. Enter the TODAY function into cell B2 to return today’s date.

3. The DATEDIF function below calculates the age of a person.

Note: fill in «y» for the third argument to get the number of complete years between the date of birth and today’s date.

4. Calculate the age of a person without displaying today’s date.

5. Calculate the age of a person on a specific date.

Note: the DATE function accepts three arguments: year, month and day.

6. The age formula below calculates the age of a person in years, months and days.

Note: fill in «ym» for the third argument to ignore years and get the number of months between two dates. Fill in «md» for the third argument to ignore months and get the number of days between two dates. Use the & operator to join strings.

Watch Video – How to Calculate Age in Excel (in Years, Months, and Days)

Using a combination of Excel functions and the date of birth, you can easily calculate age in Excel. You can either calculate the age till the current date or between the specified period of time.

The technique shown here can also be used in other situations such as calculating the duration of a project or the tenure of the service.

How to Calculate Age in Excel

In this tutorial, you’ll learn how to calculate age in Excel in:

• The number of years elapsed till the specified date.
• The number of Years, Months, and Days elapsed till the specified date.

You can also download the Excel Age Calculator Template.

Calculate Age in Excel – Years Only

Suppose you have the date of birth in cell B1, and you want to calculate how many years have elapsed since that date, here is the formula that’ll give you the result:

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

If you have the current date (or the end date) in a cell, you can use the reference instead of the TODAY function. For example, if you have the current date in cell B2, you can use the formula:

=DATEDIF(B1,B2,"Y")

DATEDIF function is provided for the compatibility with Lotus 1-2-3.

One of the things that you’ll notice when you use this function is that there is no IntelliSense available for this function. No tooltip appears when you use this function.

This means that while you can use this function in Excel, you need to know the syntax and how many arguments this function takes.

If you’re interested in knowing more about DATEDIF function, read the content of the box below. If not, you can skip this and move to the next section.

Syntax of DATEDIF function:

=DATEDIF(start_date, end_date, unit)

It takes 3 arguments:

• start_date: It’s a date that represents the starting date value of the period. It can be entered as text strings in double-quotes, as serial numbers, or as a result of some other function, such as DATE().
• end_date: It’s a date that represents the end date value of the period. It can be entered as text strings in double-quotes, as serial numbers, or as a result of some other function, such as DATE().
• unit: This would determine what type of result you get from this function. There are six different output that you can get from the DATEDIF function, based on what unit you use. Here are the units that you can use:
  • “Y” – returns the number of completed years in the specified time period.
  • “M” – returns the number of completed months in the specified time period.
  • “D” – returns the number of completed days in the specified period.
  • “MD” – returns the number of days in the period, but doesn’t count the ones in the Years and Months that have been completed.
  • “YM” – returns the number of months in the period, but doesn’t count the ones in the years that have been completed.
  • “YD” – returns the number of days in the period, but doesn’t count the ones in the years that have been completed. 

You can also use the YEARFRAC function to calculate the age in Excel (in years) in the specified date range.

Here is the formula:

=INT(YEARFRAC(B1,TODAY()))

The YEARFRAC function returns the number of years between the two specified dates and then the INT function returns only the integer part of the value.

NOTE: It’s a good practice to use the DATE function to get the date value. It avoids any erroneous results that may occur when entering the date as text or any other format (which is not an acceptable date format).

Calculate Age in Excel – Years, Months, & Days

Suppose you have the date of birth in cell A1, here are the formulas:

To get the year value:

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

To get the month value:

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

To get the day value:

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

Now that you know how to calculate the years, months and days, you can combine these three to get a text that says 26 Years, 2 Months, and 13 Days. Here is the formula that will get this done:

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

Note that the TODAY function is volatile and its value would change every day whenever you open the workbook or there is a change in it. If you want to keep the result as is, convert the formula result to a static value.

Download the Excel Age Calculator Template

Excel Functions Used:

Here is a list of functions used in this tutorial:

• DATEDIF() – This function calculates the number of days, months, and years between two specified dates.
• TODAY() – It gives the current date value.
• YEARFRAC() – It takes the start date and the end date and gives you the number of years that have passed between the two dates. For example, if someone’s date of birth is 01-01-1990, and the current date is 15-06-2016, the formula would return 26.455. Here the integer part represents the number of years completed, and the decimal part represents additional days that have passed after 26 years.
• DATE() – It returns the date value when you specify the Year, Month, and Day value arguments.
• INT() – This returns the integer part of a value.

You May Also Like the Following Excel Tutorials:

• Free Excel Holiday Calendar Template.
• Calculating Working Days between Two Dates in Excel.
• Excel Calendar Template.
• How to Automatically Insert Date and Time Stamp in Excel.
• Calculate Months Between Two Dates in Excel
• How to SUM values between two dates in Excel
• Get Day Name from Date in Excel

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 calculate David’s age:

=DATEDIF(A2, TODAY(), "y") & " year(s)" //current age in years
=DATEDIF(A2, TODAY(), "m") & " month(s)" //current age in months
=DATEDIF(A2, TODAY(), "d") & " day(s)" //current age in days

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.

Calculating Age as on a Given Date in the Past

We can also tweak the DATEDIF function slightly and calculate 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:

=DATEDIF(A2, DATE(2018,12,25), "y") & " year(s)" //past age in years
=DATEDIF(A2, DATE(2018,12,25), "m") & " month(s)" //past age in months
=DATEDIF(A2, DATE(2018,12,25), "d") & " day(s)" //past age in days


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.

Calculating 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:

=DATEDIF(A2, DATE(2045,12,25), "y") & " year(s)" //future age in years
=DATEDIF(A2, DATE(2045,12,25), "m") & " month(s)" //future age in months
=DATEDIF(A2, DATE(2045,12,25), "d") & " day(s)" //future age in days


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.

Calculating Age in Years with the YEARFRAC function

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:

=INT(YEARFRAC(A2, TODAY()))

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

Calculating Age in Years, Months, and Days

The previous formulas helped David calculate 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:

=DATEDIF(A2,TODAY(),"y") &" year(s) " & DATEDIF(A2,TODAY(),"ym") &" month(s) " & DATEDIF(A2,TODAY(),"md") &" day(s) "


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 ages 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:

=DATE(YEAR(A2) + 35, MONTH(A2), DAY(A2)) //When David turns 35 years old
=DATE(YEAR(A3) + 45, MONTH(A2), DAY(A2)) //When David turns 45 years old
=DATE(YEAR(A2) + 55, MONTH(A2), DAY(A2)) //When David turns 55 years old


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.

Calculating 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:

=DATEDIF(A2,B2,"y") & " year(s)"

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:

=INT(ABS((A2-B2)/365)) &" year(s)"

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.