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.
Let’s get started! 😊
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!
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.
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:
Applying the formula will give us:
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.
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”)
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.
Feel free to download the exercise file we included in this tutorial so you can follow the methods we described above step-by-step. 😊