Excel Age Calculation: Between Two Different Dates or From Date of BirthFormulas, Examples and More!

Written By:
Adiste Mae

There are many formulas and methods that can be used to get an output based only on the date of birth.

We will cover the different ways of computing for age – to exactly obtain the number of years, months, and days since the date of birth, or only get the years passed. 

We will discuss the different ways Excel users can convert to age the given birth date, including the benefits and disadvantages you will encounter for each of the different ways. 

Age Calculation from Birth Date

Asking a person how old he or she is, is probably the easiest way to know the age of a person.

In Excel, there is no need to ask – when you have the date of birth, you can determine the age in years, or if you want to be more precise, the years, months, and days. 

Compute Age as Whole Years

Basic Formula

Getting the age in years is made simple by subtracting the current date from the date of birth.

Suppose you have the date of birth in column B (B2), you may use the below formula:

=(TODAY()-B2)/365

Where:

  • (TODAY()-B2) – refers to the current date less the date of birth
  • 365 – allows you to obtain the number of days in a year

Here’s a slight mishap: there’s a possibility that the result is shown as a decimal instead of a whole number.

Refer to the screenshot below:

You don’t have to worry, though. You can use the integer (INT) function to round it off to a whole number.

The formula is shown below:

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

Here’s the disadvantage: the divisor (365) is not accurate.

In the case of leap years, those who were born on February 29th will become a day older in the result.

Instead of dividing the date by 365, you may instead use 365.25. 

But, when you are calculating the age of the child who was not yet lived during a leap year, the 365.25 divisor would have been wrong to use. 

While this may be the most practical to use when dealing with real-life situations, it may not be the most accurate.

Let us further explore different methods in Excel. 

Using YEARFRAC Function

We are going to use the YEARFRAC Function to compute a person’s age in years, months, and days based only on the date of birth. 

Here’s the syntax to use the YEARFRAC function:

YEARFRAC(start_date, end_date, [basis])

Where:

Start_date – refers to the cell where the date of birth is entered

End_date – the TODAY () function is used here to refer to the current date

Basis – the month’s actual days are divided by the year’s actual days used when you utilize 1 as the basis

Basing it on the above syntax, the formula to use based on the example we have provided will be:

=YEARFRAC(B2,TODAY(),1)

If you want to get rid of the decimal places, we can use the ROUNDDOWN function and the last argument should be typed as 0 to indicate that you do not want any decimal places. 

Below is the formula to use the ROUNDDOWN function:

=ROUNDDOWN(YEARFRAC(B2,TODAY(),1),0)

Use of DATEDIF Function

Here’s the syntax to use this function:

DATEDIF(start_date, end_date, unit)

Using this formula, the result will depend on what you input in the formula so you can get the difference between two different dates. 

Here are the time units you can use (in years, months, or days) so it returns the difference between the two dates provided:

  • Y – whole years
  • M – whole months
  • D – days 
  • YM – months, years, and days excluded
  • MD – days, years, and months excluded
  • YD – days, excludes years

Now we can use the formula below to calculate the age based on the date of birth:

=DATEDIF(B2,TODAY(),”y”)

Compute Age as Years, Months, and Days

Still using the DATEDIF function above and the units provided, we will be able to get the age in years, months, and days. 

Below are the formulas you can use to get the age in Years, Months, and Days:

  • Years: =DATEDIF(B2, TODAY(), “Y”)
  • Months: =DATEDIF(B2, TODAY(), “YM”)
  • Days: =DATEDIF(B2,TODAY(),”MD”)

Once we have the formula above, we proceed to use the CONCATENATE function. Now the formula will look like this:

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

The above result is difficult to understand so we have to tweak this formula a bit more and define the meaning of the values.

Try this instead:

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

Now the above is clear and easily understandable. 

Excel Formulas Specific to Calculating Age

In case you wish to employ a more specific formula to compute the age, the below formulas in the next section are highlighted. 

Age Computed Based on a Specific Date

Should you wish to know the age based on a date specified, you will still use the DATED IF formula but the second argument will be replaced with the date you have specified to compute from the date of birth. 

Let’s say you wish to know the age if the date is December 21, 2025:

=DATEDIF(B1,”12/31/2025″,”Y”)&”Years,”&DATEDIF(B1,”12/31/2025″,”YM”)&”Months,”&DATEDIF(B1,”12/31/2025″,”MD”)&”Days”

Instead of manually inputting the date, you can add the type to a cell and reference it.

Suppose we have the date of birth in column B, we can add December 31, 2025 in column C. 

The formula now would be:

=DATEDIF(B2,C2,”Y”)&”Years,”&DATEDIF(B2,C2,”YM”)&”Months,”&DATEDIF(B2,C2,”MD”)&”Days”

Age Computed Based Only on a Given Year

This is important when the only information you are only given is the year of birth. 

For example, you are currently checking the age when certain patients had their last general check-up and you only have the year of the last check-up.

Still based on the information given above, assume the last check-up year is in column C. 

Here’s the formula we will use:

=DATEDIF(B2,DATE(C2,1,1),”y”)

Formula to Calculate the Date When a Person Reaches a Certain Age

Knowing when a person is a certain age is easy to do – you can simply ask the person how old he or she is and just do the math. 

In Excel, this can be computed through a formula with the use of the DATE function.

Suppose you want to know when each person reaches the age of 80. Still using our previous examples, see below for the formula:

=DATE(YEAR(B2)+80,MONTH(B2),DAY(B2))

Age Computed Based on Different Cells for Month, Day & Year

It is a real inconvenience when the birthdate is split into three different cells.

But it isn’t when you know exactly the right formula to use.

For this, there are two functions we need to use: DATE and DATEVALUE. 

Suppose the month is in column B, the day in Column C, and the Year in Column D. The syntax of the formula is:

DATE(B2,MONTH(DATEVALUE(C2&”1″)),D2)

Adding the above using the DATEDIF function, we are going to get the complete formula as follows:

=DATEDIF(DATE(B2,MONTH(DATEVALUE(C2&”1″)), D2),TODAY(),”y”)&”Years,”&DATEDIF(DATE(B2,MONTH(DATEVALUE(C2&”1″)),D2),TODAY(),”ym”)&”Months,”&DATEDIF(DATE(B2,MONTH(DATEVALUE(C2&”1″)),D2),TODAY(),”md”)&”Days”

Conclusion

The above are just some of the formulas you can use to compute a person’s age.

We hope that you found the formulas useful and easy to execute.