How to Calculate Age on Microsoft Excel Using Functions

Quick tutorial for finding someone's age in Excel

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 wikiHow 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.
Method 1
Method 1 of 5:

Using YEAR

  1. Image titled Calculate Age on Excel Step 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. Image titled Calculate Age on Excel Step 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. Image titled Calculate Age on Excel Step 3
    3
    Create an "Age" column. This column will display the age for each entry after you enter the formula.
  4. Image titled Calculate Age on Excel Step 4
    4
    Select the first empty cell in the "Age" column. This is where you'll enter the formula to calculate age.
  5. Image titled Calculate Age on Excel Step 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.
    6. Advertisement
Method 2
Method 2 of 5:

Using YEARFRAC

  1. Image titled Calculate Age on Excel Step 6
    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. Image titled Calculate Age on Excel Step 7
    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. Image titled Calculate Age on Excel Step 8
    3
    Create an "Age" column. This column will display the age for each entry after you enter the formula.
  4. Image titled Calculate Age on Excel Step 9
    4
    Select the first empty cell in the "Age" column. This is where you'll enter the formula to calculate age.
  5. Image titled Calculate Age on Excel Step 10
    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:[1]
    • =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.
    6. Advertisement
Method 3
Method 3 of 5:

Using DATEDIF

  1. Image titled Calculate Age on Excel Step 11
    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. Image titled Calculate Age on Excel Step 12
    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. Image titled Calculate Age on Excel Step 13
    3
    Create an "Age" column. This column will display the age for each entry after you enter the formula.
  4. Image titled Calculate Age on Excel Step 14
    4
    Select the first empty cell in the "Age" column. This is where you'll enter the formula to calculate age.
  5. Image titled Calculate Age on Excel Step 15
    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:[2]
    • =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. Image titled Calculate Age on Excel Step 16
    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. Image titled Calculate Age on Excel Step 17
    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.
    8. Advertisement
Method 4
Method 4 of 5:

Using DAYS

  1. Image titled Calculate Age on Excel Step 18
    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. Image titled Calculate Age on Excel Step 19
    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. Image titled Calculate Age on Excel Step 20
    3
    Create an "Age" column. This column will display the age for each entry after you enter the formula.
  4. Image titled Calculate Age on Excel Step 21
    4
    Select the first empty cell in the "Age" column. This is where you'll enter the formula to calculate age.
  5. Image titled Calculate Age on Excel Step 22
    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.
    6. Advertisement
Method 5
Method 5 of 5:

Calculating a Date at an Age

  1. Image titled Calculate Age on Excel Step 23
    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. Image titled Calculate Age on Excel Step 24
    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. Image titled Calculate Age on Excel Step 25
    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. Image titled Calculate Age on Excel Step 26
    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.
    5. Advertisement

Expert Q&A

  • Question
    Once I have the corretc results, How can I have the average age?
    Kyle Smith
    Kyle Smith
    wikiHow Technology Writer
    Kyle Smith is a wikiHow 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
    Kyle Smith
    wikiHow 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).
  • Question
    Does the formula to calculate age work for a person born in 1929 or earlier?
    Community Answer
    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.
Advertisement

Sample Calculator

You Might Also Like

Make a Spreadsheet in Excel
How to
Make a Spreadsheet in Excel
Use ExcelNew to Excel? Here's Super Easy Tricks to Get You Started
Create a Mortgage Calculator With Microsoft Excel
How to
Create a Mortgage Calculator With Microsoft Excel
Edit Data in Microsoft Excel
How to
Edit Data in Microsoft Excel
Format a Cell in Microsoft Excel
How to
Format a Cell in Microsoft Excel
Unprotect an Excel Sheet
How to
Unprotect an Excel Sheet
Create a Timeline in ExcelHow to Create a Timeline in Excel: SmartArt, Templates, and More
Create a Graph in Excel
How to
Create a Graph in Excel
Change from Lowercase to Uppercase in Excel
How to
Change from Lowercase to Uppercase in Excel
Insert Pictures in Excel That Automatically Size to Fit Cells
How to
Insert Pictures in Excel That Automatically Size to Fit Cells
How to
Unhide Rows in Excel
Merge Cells in ExcelHow to Merge Cells in Microsoft Excel: A Quick Guide
Convert Excel to Word3 Easy Ways to Convert Microsoft Excel Data to Word
Find Duplicates in Excel
How to
Find Duplicates in Excel
Advertisement

References

  1. ↑ https://support.microsoft.com/en-us/office/yearfrac-function-3844141e-c76d-4143-82b6-208454ddc6a8
  2. ↑ https://blog.udemy.com/how-to-calculate-age-in-excel/

About This Article

Kyle Smith
Written by:
Kyle Smith
wikiHow Technology Writer
This article was co-authored by wikiHow staff writer, Kyle Smith. Kyle Smith is a wikiHow 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. This article has been viewed 529,127 times.
How helpful is this?
Co-authors: 12
Updated: March 13, 2023
Views: 529,127
Categories: Microsoft Excel
Advertisement