How to Calculate Age from Date of Birth in Excel

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

As of now, Excel has no special function that would let you calculate the age using the date of birth.

However, just because there’s no special function for it doesn’t mean you can’t do it in Excel.

With the proper combination of few functions, it’s possible to know the age from the date of birth quickly.

In this article, we’ll show you how to quickly calculate the age from date of birth.

Kasper Langmann, Co-founder of Spreadsheeto

Let’s get started! 😊

Table of Content

Free video on finding age from birth date

Watch my video and learn how to calculate a person’s age from their date of birth.

Prefer text over video? Then continue below!

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Download the FREE Exercise File

Download exercise file
Download free exercise file

The ‘YEARFRAC’ function

The most commonly used method in computing age from date of birth in Excel is through the ‘YEARFRAC’ function, coupled with the ‘INT’ and ‘TODAY’ functions.

Excel’s ‘YEARFRAC’ function returns a decimal value which is the fractional years between two dates.

It’s syntax is:

=YEARFRAC (start_date, end_date, [basis])

Parameters:

  • ‘start_date’ – the start date
  • ‘end_date’ – the end date
  • ‘basis’ – optional; the type of day count basis; default is 0 where days between 2 dates are counted in a 360-day year with all months considered having 30 days; 1 uses the actual days

To use the ‘YEARFRAC’ function as we intended to use it, all we have to do is supply the ‘start_date’ with the birthdate and the ‘end_date’ with today’s date using the ‘TODAY’ function.

Kasper Langmann, Co-founder of Spreadsheeto

Since the result would be in decimal, we would wrap the ‘YEARFRAC’ function with the ‘INT’ function which turns a decimal to an integer.

With that, here’s the formula we’ll be arriving at:

=INT(YEARFRAC(date of birth,TODAY(), 1))

Now, let’s try the formula on some real data:

dataset with names, date or birth, and age

Applying the formula will give us:

calculating the age from the date of birth using the yearfrac function

The ‘DATEDIF’ function

The ‘DATEDIF’ function is another function we can use to calculate the age with the date of birth.

However, the ‘DATEDIF’ function is sort of unique since Excel won’t help you fill-out the formula once you type it. But don’t worry, the function will work just fine once you used the correct parameters.

Kasper Langmann, Co-founder of Spreadsheeto

This function simply returns the difference between 2 date values in years, months, or days.

Here’s its syntax:

=DATEDIF (start_date, end_date, unit)

Parameters:

  • ‘start_date’ – the start date
  • ‘end_date’ – the end date
  • ‘unit’ – the unit of time to use (years, months, or days)

These are what you can input in the unit argument:

  • “Y”: shows the year between start_date and end_date
  • “M”: shows the months between start_date and end_date
  • “D”: shows the days between start_date and end_date
  • “YM”: shows months without days and years
  • “MD”: shows days without months and years
  • “YD”: shows days without years

To calculate the age, you’ll have to use “Y” as the unit argument.

Using the same example as the previous section, we’ll arrive at this formula:

=DATEDIF(C3,TODAY(),”Y”)

calculating age from date of birth using the datedif function

Wrapping things up…

Getting the age through the date of birth should not stress you out. It’s quite easy and simple.

There are a lot more ways on getting the age through the date of birth but we focused only on the basic ones as you can start your projects right away and not overwhelm you with much more syntax.

Kasper Langmann, Co-founder of Spreadsheeto

Feel free to download the exercise file we included in this tutorial so you can follow the methods we described above step-by-step. 😊