Looking for ways to know how to calculate age in Excel in years and months? Then, this is the right place for you. Sometimes, calculate age from a given date of birth. Here, you will find 5 different step-by-step explained ways to calculate age in years and months in Excel.
Here, we have the following dataset containing the Name and Date of Birth (DOB) of some workers. We will show some step-by-step methods to calculate their age in Excel in years and months.
1. Using Excel DATEDIF Function to Calculate Age in Years and Months in Excel
For the first method, we will use the DATEDIF function to calculate age in years and months in Excel. This function will calculate the age using today’s date.
Follow the steps to calculate age in your own dataset.
Steps:
- To begin with, select Cell D5.
- Then, type the following formula.
=DATEDIF(C5,$C$14,"y")&" Years "&DATEDIF(C5,$C$14,"ym")&" Months "
Here, in the DATEDIF function, we selected cell C5 as start_date, and cell C14 as end_date. To calculate the year and month we used “y” and “ym” respectively as units.
- Now, press ENTER to get the value of age in year and month.
- Then, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.
- Finally, you will get the values of age calculated by using the DATEDIF function.
2. Combining DATEDIF and TODAY Functions to Calculate Age in Excel
For the second method, we will use the DATEDIF and TODAY functions to calculate age in years and months in Excel. Here, the TODAY function returns the date of today.
Follow the steps to calculate age in your own dataset.
Steps:
- To start with, select Cell D5.
- Then, type the following formula.
=DATEDIF(C5,TODAY(),"y")&" Years "&DATEDIF(C5,TODAY(),"ym")&" Months "
Here, in the DATEDIF function, we selected cell C5 as start_date and used the TODAY function to get today’s date as end_date. To calculate the year and month we used “y” and “ym” respectively as units.
- Now, press ENTER to get the value of age in year and month.
- Then, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.
- Finally, you will get the values of age calculated by using the DATEDIF and TODAY functions.
3. Using DATEDIF and DATE Functions to Calculate Age in Years and Months
We can also use the DATEDIF and the DATE functions to calculate age in years and months. Follow the given steps to understand how to apply these functions.
Steps:
- First, select Cell D5.
- Then, type the following formula.
=DATEDIF(C5,DATE(2022,6,13),"Y")&" Years "&DATEDIF(C5,DATE(2022,6,13),"YM")&" Months"
Here, in the DATE function, we used today’s date. In the DATEDIF function, we selected cell C5 as start_date and used the DATE function as end_date. To calculate the year and month we used “y” and “ym” respectively as units.
- Now, press ENTER to get the value of age in year and month.
- Then, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.
- Finally, you will get the values of age calculated by using the DATEDIF and DATE functions.
Read More: How to Calculate Age in Excel in dd/mm/yyyy
4. Using YEARFRAC Function to Calculate Age in Excel
We can calculate age in year in Excel using the YEARFRAC function. Then, from the values of the year, we can convert them to months.
Follow the given steps to understand how to apply these functions.
Steps:
- First, select Cell D5.
- Then, type the following formula.
=YEARFRAC(C5,TODAY(),1)
Here, in the YEARFRAC function, we used C5 as start_date, TODAY function as end_date, and 1 as a basis.
- Now, press ENTER to get the value of age in the year.
- Then, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.
- Finally, you will get the values of Age in years.
- Now, select cell E5.
- Then, type the following formula.
=D5*12
- Now, press ENTER to get the value of age in the year.
- Then, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.
- Finally, you will get the values of age in months.
5. Using Combined Excel Formula to Calculate Age in Years and Months
For the last method, we will use the YEAR, MONTH, and NOW functions to calculate age in years and months in Excel. Follow the steps given below to do it on your own.
Steps:
- First, select Cell D5:E12.
- Then, from the Home tab go to Number.
- After that, select General.
- Now, select cell D5.
- Then, type the following formula.
=(YEAR(NOW())-YEAR(C5))*12+MONTH(NOW())-MONTH(C5)
Here, at first, we calculated the year difference between the DOB and NOW. NOW returns the date and time of today. Then, we converted it into months and added this value with the rest of the months.
- Now, press ENTER to get the value of age in the year.
- Then, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.
- Finally, you will get the values of Age in months.
- Now, select cell E5.
- Then, type the following formula.
=D5/12
- After that, press ENTER to get the value of age in the year.
- Then, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.
- Finally, you will get the values of age in Years.
Read More: Excel Formula to Calculate Age on a Specific Date
Download Practice Workbook
Conclusion
So, in this article, you will find 5 ways to calculate age in years and months in Excel. Use any of these ways to accomplish the result in this regard. Hope you find this article helpful and informative. Feel free to comment if something seems difficult to understand. Let us know any other approaches which we might have missed here.
I am a simple user without much knowledge on the subject.
For a long time I was looking for a formula that would give an age that would include years and months together. After realizing that the caption that appears can be changed, I was infinitely happy. Many many many thanks
Dear Rafi,
Thanks for your appreciation.
Regards
ExcelDemy