How to Calculate Age on Microsoft Excel Using Functions

Download Article

Quick tutorial for finding someone's age in Excel

Download Article

• Using YEAR
|
• Using YEARFRAC
|
• Using DATEDIF
|
• Using DAYS
|
• Calculating a Date at an Age
|
• Video
|
• Expert Q&A
|
• Tips

|Show more|Show less

Trying to figure out someone's age using Excel? You can calculate age by using functions and dates. The DATEDIF and DAYS function will calculate the difference between two specified dates. The YEAR and YEARFRAC functions work well if you just need the age in years. You can also use the DATE function to calculate the date someone will turn a certain age. This How.com.vn guide will show you how to calculate age using Microsoft Excel.

Things You Should Know

• Use the DATEDIF function to calculate the time between someone's birth date and another date.
• You can calculate how old someone is in years, months, or days using DATEDIF.
• YEAR and YEARFRAC return the number of years between two dates.
• Use the DATE function to find out what date someone will turn a certain age.

Steps

Method 1

Method 1 of 5:

Using YEAR

Download Article

1. 
   1
   Create a "Name" column and a "Birthday" column. These columns will allow you to keep track of the age for a list of people. The "Birthday" column will contain a birthdate for each individual.
2. 
   2
   Enter the birthdates using a common format. Make sure each birthday is entered using the same format, such as MM/DD/YYYY. Excel should automatically detect that you are entering dates, and will format the data accordingly.

   • If the data is being auto-formatted as something else, highlight the cells and click the drop-down menu in the "Numbers" section of the Home tab. Select "Short Date" from the list of options.
   • If you need to change the date to a different format (like DD/MM/YYYY):
   • Go to Home and click the Number Format button in the Numbers section.
   • Go to the Date tab and change "Locale" to the country with the correct formatting.
   Advertisement
3. 
   3
   Create an "Age" column. This column will display the age for each entry after you enter the formula.
4. 
   4
   Select the first empty cell in the "Age" column. This is where you'll enter the formula to calculate age.
5. 
   5
   Type the YEAR formula to calculate age in years. The YEAR function returns the year of a given date. This formula subtracts the current year from the birth year. Type the following into the cell:

   • =(YEAR(NOW())-YEAR(B2))
   • Replace B2 with the cell containing the birth date.
   • Press ↵ Enter to confirm the formula and calculate the age.
   • You can click and drag the small square down to copy the formula to the cells below.
Advertisement

Method 2

Method 2 of 5:

Using YEARFRAC

Download Article

1. 
   1
   Create a "Name" column and a "Birthday" column. These columns will allow you to keep track of the age for a list of people. The "Birthday" column will contain a birthdate for each individual.
2. 
   2
   Enter the birthdates using a common format. Make sure each birthday is entered using the same format, such as MM/DD/YYYY. Excel should automatically detect that you are entering dates, and will format the data accordingly.

   • If the data is being auto-formatted as something else, highlight the cells and click the drop-down menu in the "Numbers" section of the Home tab. Select "Short Date" from the list of options.
   • If you need to change the date to a different format (like DD/MM/YYYY):
   • Go to Home and click the Number Format button in the Numbers section.
   • Go to the Date tab and change "Locale" to the country with the correct formatting.
3. 
   3
   Create an "Age" column. This column will display the age for each entry after you enter the formula.
4. 
   4
   Select the first empty cell in the "Age" column. This is where you'll enter the formula to calculate age.
5. 
   5
   Type the YEARFRAC formula to find the age in years. This function calculates the fraction of the year between the specified dates. For example, the fractional years between 1/1/1990 and 3/25/2023 is 33.23. This indicates there are 33 years and 23 percent of a year between the dates. To find an age, use this formula:

   • =YEARFRAC(B2,TODAY(),1)
   • Replace B2 with the cell containing the birth date.
   • The 1 indicates that the function should use the actual day count, instead of a different standard like 30/360.
   • Press ↵ Enter to confirm the formula and calculate the age.
   • You can click and drag the small square down to copy the formula to the cells below.
Advertisement

Method 3

Method 3 of 5:

Using DATEDIF

Download Article

1. 
   1
   Create a "Name" column and a "Birthday" column. These columns will allow you to keep track of the age for a list of people. The "Birthday" column will contain a birthdate for each individual.

   • This is great for creating a database of ages for a group of people using Excel. You could then use VLOOKUP to quickly find a specific person's age.
2. 
   2
   Enter the birthdates using a common format. Make sure each birthday is entered using the same format, such as MM/DD/YYYY. Excel should automatically detect that you are entering dates, and will format the data accordingly.

   • If the data is being auto-formatted as something else, highlight the cells and click the drop-down menu in the "Numbers" section of the Home tab. Select "Short Date" from the list of options.
   • If you need to change the date to a different format (like DD/MM/YYYY):
   • Go to Home and click the Number Format button in the Numbers section.
   • Go to the Date tab and change "Locale" to the country with the correct formatting.
3. 
   3
   Create an "Age" column. This column will display the age for each entry after you enter the formula.
4. 
   4
   Select the first empty cell in the "Age" column. This is where you'll enter the formula to calculate age.
5. 
   5
   Enter the formula for calculating the age in years. Type the following formula, which assumes that the first birthday is listed in cell B2:

   • =DATEDIF(B2,TODAY(),"Y")
   • DATEDIF is a function that calculates the difference between two dates. (B2,TODAY(),"Y") tells DATEDIF to calculate the difference between the date in cell B2 (the first birthday listed) and the current date (TODAY()). It outputs the calculation in years ("Y").
   • If you'd rather see the age in days or months, use "D" or "M" instead.
   • If you want to calculate someone's age on a specific date, change TODAY() to a cell reference containing that date.
6. 
   6
   Click and drag the square in the bottom-right corner of the cell down. This will copy and apply the same formula to each line, adjusting it accordingly so that the correct birthday is calculated.

   • You're done! Now you can create a graph of ages or perform data analytics on the dataset.
7. 
   7
   Troubleshoot a formula that isn't working. If the formula is displaying something like #VALUE! or #NAME?, then there is likely an error somewhere in the formula. Make sure that the syntax is exactly correct, and that you are pointing to the correct cells in the spreadsheet. Note that the DATEDIF() formula does not work for dates before 01/01/1900.
Advertisement

Method 4

Method 4 of 5:

Using DAYS

Download Article

1. 
   1
   Create a "Name" column and a "Birthday" column. These columns will allow you to keep track of the age for a list of people. The "Birthday" column will contain a birthdate for each individual.
2. 
   2
   Enter the birthdates using a common format. Make sure each birthday is entered using the same format, such as MM/DD/YYYY. Excel should automatically detect that you are entering dates, and will format the data accordingly.

   • If the data is being auto-formatted as something else, highlight the cells and click the drop-down menu in the "Numbers" section of the Home tab. Select "Short Date" from the list of options.
   • If you need to change the date to a different format (like DD/MM/YYYY):
   • Go to Home and click the Number Format button in the Numbers section.
   • Go to the Date tab and change "Locale" to the country with the correct formatting.
3. 
   3
   Create an "Age" column. This column will display the age for each entry after you enter the formula.
4. 
   4
   Select the first empty cell in the "Age" column. This is where you'll enter the formula to calculate age.
5. 
   5
   Use the DAYS formula to calculate age. This function returns the number of days between two dates. To find someone's age today:

   • =DAYS(TODAY(),B2)
   • Replace B2 with the cell containing the birth date.
   • Press ↵ Enter to confirm the formula and calculate the age.
   • You can click and drag the small square down to copy the formula to the cells below.
Advertisement

Method 5

Method 5 of 5:

Calculating a Date at an Age

Download Article

1. 
   1
   Create a "Name" column and a "Birthday" column. These columns will allow you to keep track of the age for a list of people. The "Birthday" column will contain a birthdate for each individual. This method calculates the date someone will turn a certain age.
2. 
   2
   Enter the birthdates using a common format. Make sure each birthday is entered using the same format, such as MM/DD/YYYY. Excel should automatically detect that you are entering dates, and will format the data accordingly.

   • If the data is being auto-formatted as something else, highlight the cells and click the drop-down menu in the "Numbers" section of the Home tab. Select "Short Date" from the list of options.
   • If you need to change the date to a different format (like DD/MM/YYYY):
   • Go to Home and click the Number Format button in the Numbers section.
   • Go to the Date tab and change "Locale" to the country with the correct formatting.
3. 
   3
   Create a "Future Date" column. This column will contain the formula for calculating what date it will be when someone turns a specific age.
4. 
   4
   Use the DATE function to calculate the date. The DATE function uses a year, month, and day to calculate the date at that specified time. You can use the function to find the date someone turns a certain age by adding that many years to their birth year. For example, to calculate when someone will turn 20:

   • =DATE(YEAR(B2)+20, MONTH(B2), DAY(B2))
   • This assumes the birthdays are in column B.
   • Change the "20" to another age to calculate a different date.
Advertisement

Expert Q&A

Search

Add New Question

• Question

  Once I have the corretc results, How can I have the average age?

  

  Kyle Smith
  How.com.vn Technology Writer

  Kyle Smith is a How.com.vn Technology Writer, learning and sharing information about the latest technology. He has presented his research at multiple engineering conferences and is the writer and editor of hundreds of online electronics repair guides. Kyle received a BS in Industrial Engineering from Cal Poly, San Luis Obispo.

  

  Kyle Smith

  How.com.vn Technology Writer

  Expert Answer

  You can use the AVERAGE function to calculate the average age of the list of ages you created. For example, if the ages are in column B rows 2 through 20, you would write =AVERAGE(B2:B20).

  Thanks! We're glad this was helpful.
  Thank you for your feedback.
  If How.com.vn has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission.Support How.com.vn

  YesNo

  Not Helpful 0Helpful 0
• Question

  Does the formula to calculate age work for a person born in 1929 or earlier?

  

  Community Answer

  I've tested the formula using Excel 2013 and it worked correctly. Here is the formula I used: =DATEDIF(A2,TODAY(),"Y") where A2 was the cell with the birth date. I also used the formula as follows: =DATEDIF(A6,"31-dec-2015","Y") where A6 was the birth date and I wanted the age as of December 31, 2015; this also calculated correctly. However, the formula will not work if the date is before January 1, 1900.

  Thanks! We're glad this was helpful.
  Thank you for your feedback.
  If How.com.vn has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission.Support How.com.vn

  YesNo

  Not Helpful 20Helpful 34

Ask a Question

200 characters left

Include your email address to get a message when this question is answered.

Submit

Advertisement

Sample Calculator

Age Calculator

Video

Tips

• The "DATEDIF" function is not listed in the function wizard. You can use the "YEAR" function to return age in years, but it does not take the months and days of your dates into account.
  Thanks

  Helpful5Not Helpful5

Submit a Tip

All tip submissions are carefully reviewed before being published

Submit

Thanks for submitting a tip for review!

Advertisement

You Might Also Like

How to

Make a Spreadsheet in Excel

New to Excel? Here's Super Easy Tricks to Get You Started

How to

Create a Mortgage Calculator With Microsoft Excel

3 Simple Ways to Edit Data in MS Excel

How to

Format a Cell in Microsoft Excel

How to

Recover a Corrupt Excel File

How to

Unprotect an Excel Sheet

How to Use If‐Else in Microsoft Excel: Step-by-Step Tutorial

How to Create a Graph in Excel

How to Fix Ethernet Doesn't Have a Valid IP Configuration on Windows

How to Merge Cells in Microsoft Excel: A Quick Guide

Easily Create a Drop-Down List in Microsoft Excel: Setup & Customization

How to

Insert Pictures in Excel That Automatically Size to Fit Cells

How to Combine Columns in Excel Without Losing Data

Advertisement