How to Calculate Age in Excel in Years and Months (5 Easy Ways)

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.


Download Practice Workbook


5 Ways to Calculate Age in Excel in Years and Months

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.

Calculate Age in Excel in Years and Months


1. Using 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.

Using DATEDIF Function to Calculate Age in Years and Month in Excel

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.

Using DATEDIF Function to Calculate Age in Years and Month in Excel

  • Finally, you will get the values of Age calculated by using the DATEDIF function.

Read More: How to Calculate Average Age in Excel (7 Easy Methods)


2. Use of DATEDIF and TODAY Functions to Calculate Age in Years and Months 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.

Using DATEDIF and TODAY Functions to Calculate Age in Years and Months

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.

Using DATEDIF and TODAY Functions to Calculate Age in Years and Months

  • Finally, you will get the values of Age calculated by using the DATEDIF and TODAY functions.

Read More: How to Calculate Age from Birthday in Excel (8 Easy Methods)


3. Applying DATEDIF and DATE Functions to Calculate Age in Years and Months in Excel

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"

Using DATEDIF and DATE Functions to Calculate Age in Years and 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.

Using DATEDIF and DATE Functions to Calculate Age in Years and Months

Read More: How to Calculate Age in Excel in dd/mm/yyyy (2 Easy Ways)


4. Use of YEARFRAC Function to Calculate Age in Excel

We can calculate age in year in Excel using the YEARFRAC function. Then, from the values of year, we can convert them to months.

Using YEARFRAC Function to Calculate Age in Excel

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.Using YEARFRAC Function to Calculate Age in Excel
  • Finally, you will get the values of Age in years.

  • Now, select cell E5.
  • Then, type the following formula.
=D5*12

Using YEARFRAC Function to Calculate Age in Excel

  • 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.

Using YEARFRAC Function to Calculate Age in Excel


5. Using Combined Formula to Calculate Age in Excel

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.

Using Combined Formula to Calculate Age in Excel

  • 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.

Using Combined Formula to Calculate Age in Excel

  • Finally, you will get the values of Age in months.

Using Combined Formula to Calculate Age in Excel

  • 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.

Using Combined Formula to Calculate Age in Excel

  • Finally, you will get the values of Age in Years.

Read More: Excel formula to Calculate Age on a Specific Date


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. And, visit ExcelDemy for many more articles like this. Thank you!


Related Articles

Arin
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo