Age Function: How to Calculate Age in Excel from Date of Birth

Need a formula or function to calculate age in Excel?

You’ve found it.

In this guide, I’m going to show you some of the simple methods and functions to calculate or convert any date of birth into age. This way, you’ll be able to know the exact number of years, months or days at today’s date or a particular date.

Without any further ado, let’s get started.

What is the Age Function in Excel?

Excel has no such thing as an Age function yet. There is no specially built function to calculate age or ages in Microsoft Excel. However, with the use of other related functions, you can successfully convert any date of birth to age. In fact, the functions that are currently used does the job so well that there is no need for a new function for age.

Below explains some of the simplest methods or functions you can use to come up with age formulas.

Method #1: Using DATEDIF() and TODAY() functions

The Excel DATEDIF() function returns the difference between two date values in years, months, or days. And as we all know, you need to subtract the birthdate from the current date in order to calculate someone’s age.

So, using these two functions (DATEDIF and TODAY), we can find the difference between today’s date and any date of birth.

Thus, to write a formula that will calculate age in Excel, open Excel and obey the following steps:

• Enter the Birthdate in one cell, cell B3 for example (See screenshot below)

• Enter =Today() function in another cell, cell C3 (see screenshot below).

NOTE: If you want to calculate the age at a specific date, replace the TODAY() function with the target date.

• Now, use the DATEDIF function below in cell D3 to calculate the age (that’s the difference between the birthdate and today’s date).

(See screenshot below)

Formula Explained:

Syntax: DATEDIF(start_date,end_date,unit)

Actual Formula used=DATEDIF(B3,C3,”Y”)

The DATEDIF function includes the following arguments:

1. Start_date – This is a required argument. As the name suggests, it is the initial date of the period. In this example, this is supposed to be the birthdate, as shown in the formula.
2. End_date – This is also a required argument. It represents the last, or ending, date of the period. Here, today’s date is used. You can also use a specific date find the age at that date (it could be past or future date).
3. Unit – The time unit in which we want the information. Ages are normally calculated in years. That’s why we used the “Y” argument to give us the age in years. Using this last argument, you can calculate someone’s age in years, months, and days as shown in the next example.

How to calculate age in years, months, and days

To calculate the age of a person using all the three time-units (Years, Months, and Days), you’ll have to use the concatenate feature to join three different formulas, each for a time unit.

To begin with, below is the formula to calculate the year value for that age:

To calculate the month value of the age, use below formula:

To calculate the day value of the age, use below formula:

After getting all the three formulas ready, you can now join them into one formula to get a result that says 35y 7m 10d. Where they stand for years, m for months, and d for days.

See the example in the screenshot below:

The formula used here is stated below in full if you want to copy and paste:

Or

You can either use cell reference to refer to the current date, or you can use the TODAY() function as seen in the above formulas.

Using the DATEDIF() function is just one way of calculate someone’s age in Excel. Let’s explore more in the next option.

Option #2: Using TODAY() function

Using TODAY() function, the date formula in Excel goes like this:

Normally, to calculate someone’s age, you need to subtract the birth date from the current date. That’s the idea with the age formula above.

To make it clear in the worksheet below, let’s enter the birthdate in Cell C3 to in cell C3 through Cell C7.

The formula will now look like this:

Formula Explained:

(Today()-B2) – This is the first part of the formula. What is does is calculate the difference between today’s date and the date of birth in days. Since you are getting the difference in days, you need to divide that number by 365 to be able to get the number of years.

The formula is very simply and straight forward. The only problem is that it returns decimals. You may also notice that it returns date in your first attempt to write the formula.

If your formula returned date, it’s because you are subtracting dates in the formula. So simply change the number format of the cell with the formula from date to number. To do that go to Home, in the Number group, select Number in the Number Format drop-down.

Another small problem you may notice is the decimal numbers. To avoid the decimals and display the number of complete years, use the INT function to round the decimals down to the nearest integer.

These are some of the easiest ways and formulas to calculate someone’s age in Excel.

Please leave a comment below if you don’t get a point.