How to Calculate Age in Excel: Step-by-Step (2023 Guide)
Have you worked with data where you need to calculate someone’s age from their given birth dates? You probably came across it and manually calculated for the age 🙁
But it doesn’t have to be done that way. Although Microsoft Excel has no special function to let you quickly calculate age, it’s still super possible.
With the proper combination of a few Excel functions, you can calculate age in Excel either from birth dates or from two specified dates.
This article gives you a step-by-step guide on how to calculate age in Excel helping you skip the manual calculation and get more work done 😀
Let’s get started!
You learn better by doing. As you follow the steps below, make sure to download this free practice workbook for you to have a data set to work on.
Table of Contents
Calculate age from date of birth
When asked about your age, you calculate it from your birth date to today’s date, right? 😊
There are different methods how to calculate age in Excel. But don’t get overwhelmed by thinking you have to learn it all. We’ll only focus on the basic and most used ones.
The most commonly used method in calculating age from date of birth in Excel is through the YEARFRAC function coupled with the INT function and TODAY function.
The syntax of the YEARFRAC function is:
=YEARFRAC (start_date, end_date, [basis])
With the following arguments below:
- start_date – the start date or birth date
- end_date – the end date, use the TODAY function for today’s date or the current 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.
The YEARFRAC function returns a decimal value which is the fractional years between two dates.
Since the result would be in decimal, we would wrap the YEARFRAC function with the INT function which turns a decimal into the nearest integer.
With that, this is the final age formula we’ll be using:
=INT(YEARFRAC(birth date,TODAY(), 1))
Okay, enough technical talk, let’s try the formula with some real data 💪
Open your practice workbook and let’s calculate the age from their birth dates to today’s date.
When working with the age formula in your own workbooks, make sure that the birth dates are in date format.
In our workbook, the birth dates are already in date format. So, let’s start!
- Double-click cell C2.
- Type the above formula in our cell.
=INT(YEARFRAC(
- The start_date is the date of birth. It’s already given in the workbook. Click the cell reference instead of typing it. In our case, it’s cell B2.
=INT(YEARFRAC(B2,
- The end_date is the current date of today. For this, we’ll use the TODAY function. Type:
=INT(YEARFRAC(B2,TODAY()
- Type 1 for the basis. Complete the formula by typing two right parentheses.
=INT(YEARFRAC(B2,TODAY(), 1))
- Press Enter.
And there we have it! Majid is 29 years old 👍
Quickly calculate the ages of the others by double-clicking or dragging down the fill handle.
That’s how you calculate age in Excel from the birth date. All it took us is combining the INT, YEARFRAC, and TODAY functions into a single formula to calculate it.
See? No more manual calculations. Let Excel do it for you 😊
Calculate age from two dates
The DATEDIF function is another function we can use to calculate the age with the date of birth. But aside from the birth date, you can also calculate age from two specified dates with it.
What’s more is that using the DATEDIF function to calculate age can return the difference between two date values in years, months, or days 🤯
Here is the syntax of the DATEDIF function:
=DATEDIF (start_date, end_date, unit)
With the following arguments:
- start_date – the start date (or birth date)
- end_date – the end date (current date or another specified 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(birth date,TODAY(),”Y”)
Let’s try it 👇
- Double-click cell D2.
- Type our DATEDIF function.
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 😊
=DATEDIF(
- For the start_date or date of birth, it’s already given in the workbook. Click cell B2, then type a comma.
=DATEDIF(B2,
- For the end_date, we can use the TODAY function and then type a comma.
=DATEDIF(B2,TODAY(),
- For the unit, type “Y” to show the year between the start_date and the end_date. Then close the DATEDIF formula with a right parenthesis.
=DATEDIF(B2,TODAY(),”Y”)
- Press Enter.
That’s it. We can fill in the rest of the rows by using the fill handle 😀
Remember that with the DATEDIF function, you can calculate someone’s age not only from today’s date but also from other dates.
You can also show the age in years, months, or days with this function too.
Kasper Langmann, Microsoft Office SpecialistWhether you use the YEARFRAC function or the DATEDIF function, you can calculate someone’s age quickly in Excel. You just need to be careful to fill in the needed arguments or parameters in the formula.
Finally, your practice workbook should look like this 👇
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 get overwhelmed with much more syntax.
Kasper Langmann, Microsoft Office SpecialistThat’s it – Now what?
Great job 👍 Now you know how to calculate age in Excel with the use of Excel functions and their combinations. You don’t have to manually calculate someone’s age in Excel from now on!
That’s the beauty of Excel! It offers you built-in functions and formulas that will help you eliminate manual work and get more things done faster and easier.
If that sounds good to you, then the next step for you is to learn advanced Excel functions that you actually need in real life. Functions like the IF, SUMIF, and most especially the VLOOKUP function which is labeled as the most popular and useful Excel function everyone must know 🚀
Join my free email course today and turbocharge your skills in Excel tomorrow 😀
Other resources
Learn more about Date functions and Time functions! Read our article to learn all you need to know about Date and Time functions in Excel.
Need to know about number formatting in Excel? Click this link to learn how to format numbers into decimals, scientific notations, currency, and more.
I hope this blog post was helpful 😊