How to Calculate Age in Excel? – A Complete Beginner’s Guide

Calculate age in

In Microsoft Excel, it is possible to calculate the age of a person with the help of a set of formulas.

Let’s get started with this step-by-step guide to calculate age in Excel.

Recommended read: How to calculate the square root in Excel?

Steps to calculate age in Excel with the DATEDIF() function

Age calculation is done with the help of the DATEDIF formula in Excel that requires the date of birth and today’s date to be available in the sheet.

This is how Excel can provide you the calculated ages of people-

Age Calculation in Excel
Age Calculation in Excel

So, let’s learn to calculate the current age of a person-

  • Prepare a database with names of people in one column and their DOB (date of birth) in the adjacent column.
  • Now, create a new column called TODAY and enter today’s date formula instead of manually typing today’s date.
  • Type =TODAY() and press ENTER to get today’s date. This will automatically change the date every day to the current date on your system, without you having to manually set the current date every day.

This is how your database should look like so far-

Sample Database for Age Calculation
Sample Database for Age Calculation

Calculating age in years in Excel

Let’s begin calculating their ages in years with =DATEDIF formula-

Calculating Age in Years
Calculating Age in Years
  • Add 5 new columns to the table named- YEAR, MONTH, DAYS, DAY, and Age in Words (optional).
  • To calculate age in years, select a cell under the YEAR column of a person.
  • Type =DATEDIF( and select the cell under the DOB column for the person you want to calculate the date, then
  • Add a comma and select the cell with today’s date, then
  • Finally, add a comma and type “Y”
  • Press ENTER.

You can now see the calculated age in years.

Age in Years
Age in Years
  • You can simply drag down or double-click on the lower right corner of this cell to automatically calculate age in years for other people in the database.
All Ages in Years
All Ages in Years

Calculating age in months in Excel

Let’s begin calculating ages in months with =DATEDIF formula-

Calculating Age in Months
Calculating Age in Months
  • Select a cell under MONTH column.
  • Add 5 new columns to the table named- YEAR, MONTH, DAYS, DAY, and Age in Words (optional).
  • To calculate age in years, select a cell under the YEAR column of a person.
  • Type =DATEDIF( and select the cell under the DOB column for the person you want to calculate the date, then
  • Add a comma and select the cell with today’s date, then
  • Finally, add a comma and type “YM”
  • Press ENTER.

You can now see the calculated age in months.

Ages in Months
Ages in Months

Calculating age in days in Excel

Let’s begin calculating ages in days with =DATEDIF formula-

Calculating Age in Days
Calculating Age in Days
  • Select a cell under DAYS column.
  • To calculate age in years, select a cell under the YEAR column of a person.
  • Type =DATEDIF( and select the cell under the DOB column for the person you want to calculate the date, then
  • Add a comma and select the cell with today’s date, then
  • Finally, add a comma and type “MD”
  • Press ENTER.
  • You can now drag down or double-click on the lower right corner of this cell to automatically calculate age in years for other people in the database.

You can now see the calculated age in days.

Age in Days
Age in Days

Calculating weekday of birth

Let’s begin finding the day of the week when the person was born with =CHOOSE formula-

  • Select a cell under DAY column.
  • Type =CHOOSE( and then type WEEKDAY
  • Then, select the DOB of a person and close the bracket but don’t press ENTER yet!
  • Put a comma and type “Sun”,”Mon”,”Tues”,”Wed”,”Thurs”,”Fri”,”Sat” without spaces.

Look at the example below to understand better.

Calculating Day of Birth
Calculating Day of Birth
  • Close the bracket again and press ENTER.
  • You now have a day of birth for a person.
  • Double-click on the lower right corner of this cell to automatically calculate weekday of birth for other people in the database.

You can now see the calculated days of birth.

Calculated Weekdays of Birth
Calculated Weekdays of Birth

Expressing age in words in Excel

To express the calculated age in words, you can use the CONCATENATE formula to achieve this. Here is how-

Concatenating Age in Words
Concatenating Age in Words
  • Select a cell under Age in words.
  • Type =CONCATENATE( then select calculated age in years.
  • Put a comma and type “ “ to give a space between each value that is being concatenated.
  • Continue to type ,”years”,” “,V30,” “,”months”,” “,W30,” “,”days”).
  • Press ENTER to complete the formula.
All Ages in Words
All Ages in Words

Conclusion

This article was a complete beginner’s guide on calculating age of people in Microsoft Excel. Follow this tutorial carefully and learn how to correctly calculate age in Excel. If you have any doubts regarding age calculation, feel free to drop a comment and we will help you out!