We all did some age calculation at some point. And we all preferred using calculator mostly. But now, we have option to do it on Excel with formulas. So after learning how to do it, you may calculate your age or someone else’s age in Excel. Because Excel formulas make the process a little easier compared to a normal calculator.
Table of Contents
Here you can check out the formula for calculate age in Excel and start practicing.
How to Calculate Age in Excel (In Easy Steps): present age formula
The formula for calculating age in Excel is quite simple tough. All you need is the person’s birthdate and the current date. Here is the formula: =DATEDIF(birthdate, today,”y”)
This might look confusing at first. But it is actually quite basic once you learn to read it. The DATEDIF function calculates the difference between two dates. And “y” specifies that we want the difference in years. So all you need to do is dial a person’s birthdate and today’s date and Excel will do the rest!
calculate age based on date of birth
Of course, this only works if you have both the birthdate and today’s date in your Excel sheet. If you don’t have today’s date so to say, you can use the TODAY() function to get it. And if you don’t have the birthdate, you can either ask the person.
How to use the age calculation formula in Excel
Now, we know the formula for calculating age in Excel. But how you gonna use it in practice? First, you get your Excel sheet and select a cell. Then, you will enter the following formula into the cell: =DATEDIF(birthdate, today,”y”)
Ok now, you will replace “birthdate” with real birthday date and “today” with the current date. Once you entered the formula, you can hit Enter and Excel will calculate the age!
sum excel birth date to age
If you want to calculate the age of multiple people, you can use the same formula in each cell separately. But here you should update the cell references. So that they correspond to the correct birthdates and today’s date.
So next time when you are down to some calculation, you can reach for Excel instead of your phone!
What is the excel formula to find age?
If you have a date of birth in cell A1 and today’s date in cell B1, you can calculate age like below formula.
=DATEDIF(A1,B1,”y”)&” years old”
This will give you the number of full years between the two dates. If you want to include the number of months and days as well, you can use the below one.
=DATEDIF(A1,B1,”my”)&” years and “&DATEDIF(A1,B1,”MD”)&” days old”
how to count age in excel
If you want to get specific and include the number of hours, minutes and seconds, below function can work.
=DATEDIF(A1,B1,”you”)&” years, “&DATEDIF(A1,B1,”my”)&” months, “&DATEDIF(A1,B1,”yd”)&” days old”
But this last formula will only be accurate if the date of birth and today’s date are entered as complete dates (including time). The results will be inaccurate if either date is entered as just a day or just a month.
How do I calculate age from a specific date in Excel?
There are a few different ways you can calculate age in Excel depending on your available information.
If you have the person’s date of birth and the current date, you can subtract the two to get the person’s age in years. Ok in this situation, today is Jan 1, 2019. And your friend born on December 31, 2017. Then their age would be one year.
how to determine age in excel
If you have the person’s date of birth and calculate his or her age on a specific past or future date, you can use the DATEDIF function. This function calculates the number of days between two dates. So if you divide that by 365 (the number of days in a year), you can get the number of years between those.
For example, hypothetical birthday is Jan 31, 2017. But you want to know their age as of January 1, 2019. So you would use the following formula below.
=DATEDIF(A2,B2,”y”)
Where A2 i person’s date of birth and B2 is January 1, 2019. This would give a result of 1 since the person is one year old.
compute age in excel
You can also use the DATEDIF function there. Let’s say, your baby was born on December 31, 2017 and you want to know their age in months using January 1, 2019. So you can do below steps.
=DATEDIF(A2,B2,”my”)
A2 is person’s date of birth and B2 is the cell containing January 1, 2019. This would give a 12 as age.
How is it calculated example?
Similarly, if you want to know someone’s age in days as of a specific date, you would use the following formula:
=DATEDIF(A2,B2,”MD”)
Here tough , A2 is the cell with date of birth, and B2 is the specific date you want.
You can also use the YEARFRAC function to calculate age in years, months or days. This function gives you the fraction of a year between two dates. So if you multiply it by 365, you will get the number of days between the two dates.
Examples to Calculate Age in Excel
For instance, if you niece was born December 31, 2017 and you calculate her age at January 1, 2019. You should enter the below formula into your cell.
=YEARFRAC(A2,B2)*365
Again here, A2 is birthday and B2 is the cell containing January 1, 2019. This would give a result of 365. Because there 365 days between the person’s date of birth and January 1, 2019.
How to Calculate Age in Excel with YEARFRAC
You can also use the YEARFRAC function to calculate age in months or days. In case somebody was born on December 31, 2017 and you want to calculate their age according to January 1, 2019; then, you would use the following formula:
=YEARFRAC(A2,B2)*12
Here this would give a result of 12, since there are 12 months between the person’s date of birth and January 1, 2019.
A dedicated Career Coach, Agile Trainer and certified Senior Portfolio and Project Management Professional and writer holding a bachelor’s degree in Structural Engineering and over 20 years of professional experience in Professional Development / Career Coaching, Portfolio/Program/Project Management, Construction Management, and Business Development. She is the Content Manager of ProjectCubicle.
