Wondering How To Find the Age in Years from Date of Birth, in Microsoft Excel?
See multiple ways to calculate Age in Excel from any date of birth.
This simple, step-by-step tutorial on finding the age in Excel from date of birth or any other date, work on Microsoft Office 365, Microsoft Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007 & Excel 2003 too.
We cover Microsoft Excel Functions & Formulas that allow you to calculate the following things in Excel:
- Find the age in Years
- Find the Age in Months
- Find the Age in Days
- Finding the exact age in Years, Months & Days.
- Combining Years & Months to create a Text String the way we want.
Here’s how to calculate the number of years since the date of birth
Not only the years we can also calculate the months. I will show you multiple techniques to calculate the age in years, months & days.
If you want a whole number or you want the age as a fraction, there are so many ways to do it.
What we want to calculate is the age in years – something like Age is 55 years. Or Age is 55.56 years.
Before we start i want to highlight to you that the date of birth that you are using should be a date field so you can select it and make sure the formatting is not General or text, but it should be set as a short date or any kind of date.
To calculate the years, the formula is extremely simple. We will be using the DATEDIF function of Excel.
=DATEDIF(DateofBirth, TODAY(), “Y”)
Similarly, we can calculate the age in months too.
=DATEDIF(DateofBirth, TODAY(), “M”)
This will result in the number of months from the date of birth to today’s date. You might see a huge number like 666 here.
But you may not want this. Perhaps we only want how many months after the 55 whole years have elapsed.
In this case, we modify the formula from only “M” to “YM”
=DATEDIF(DateofBirth, TODAY(), “YM”)
YM means it is only the months after the completed years. By doing this we can see that only six months have elapsed after 55 years.
Finally, we only need the days that have elapsed after the completed months in the age.
=DATEDIF(DateofBirth, TODAY(), “D”)
This will tell you that 20,000 days have passed from the date of birth, but, of course, we don’t want this.
So you change D to MD. The “MD” tells Excel to calculate how many days after the month have elapsed.
As you can see, we have the age calculated in different cells – the age is 55 years 6 months and 9 days exactly till today.
If you wish to see a complete combined date, you can use the CONCAT function to combine multiple formulas, or use the & to combine text as well.
As an example, we can write
=CONCAT(DATEDIF(dateofbirth,TODAY(),”Y”),” Years, “,DATEDIF(dateofbirth,TODAY(),”YM”),” Months & “,DATEDIF(dateofbirth,TODAY(),”MD”),” Days”)
For a DateofBirth date of 6-December-1966, it generates, 55 Years, 6 Months & 28 Days.
Hope you find this DATEDIF function quite handy for such use. Do give it a try and let us know how you use it.
Recommended Reading for Next Steps: