How to Calculate Age on Microsoft Excel Using Functions

PDF download Download Article

Quick tutorial for finding someone's age in Excel

PDF download Download Article

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. [1] 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. [2]
  • 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. [3]
Method 1
Method 1 of 5:

Using YEAR

PDF download Download Article
  1. 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. 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. [4]
    • 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. This column will display the age for each entry after you enter the formula.
  4. This is where you'll enter the formula to calculate age.
  5. The YEAR function returns the year of a given date. [5] This formula subtracts the current year from the birth year. Type the following into the cell: [6]
    • =(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

PDF download Download Article
  1. 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. 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. This column will display the age for each entry after you enter the formula.
  4. This is where you'll enter the formula to calculate age.
  5. 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. [7] This indicates there are 33 years and 23 percent of a year between the dates. To find an age, use this formula: [8]
    • =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

PDF download Download Article
  1. 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. 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. [9]
    • 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. This column will display the age for each entry after you enter the formula.
  4. This is where you'll enter the formula to calculate age.
  5. Type the following formula, which assumes that the first birthday is listed in cell B2: [10]
    • =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. 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. 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

PDF download Download Article
  1. 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. 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. This column will display the age for each entry after you enter the formula.
  4. This is where you'll enter the formula to calculate age.
  5. This function returns the number of days between two dates. To find someone's age today: [11]
    • =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

PDF download Download Article
  1. 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. 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. [12]
    • 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. This column will contain the formula for calculating what date it will be when someone turns a specific age.
  4. 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: [13]
    • =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

Search
Add New Question
  • Question
    Once I have the corretc results, How can I have the average age?
    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
    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).
    Thanks! We're glad this was helpful.
    Thank you for your feedback.
    If wikiHow 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 wikiHow
    Yes No
    Not Helpful 0 Helpful 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 wikiHow 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 wikiHow
    Yes No
    Not Helpful 20 Helpful 34
Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
      Advertisement

      Sample 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
        Helpful 5 Not Helpful 5
      Submit a Tip
      All tip submissions are carefully reviewed before being published
      Name
      Please provide your name and last initial
      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
      Easy Steps to Change the Format of a Cell in Microsoft Excel
      How to
      Merge Cells in Microsoft Excel: A Quick Guide
      How to
      Unprotect an Excel Sheet
      How to
      Unhide All Rows and Columns in Microsoft Excel
      How to
      Use If‐Else in Microsoft Excel: Step-by-Step Tutorial
      How to
      Insert Pictures in Excel That Automatically Size to Fit Cells
      How to
      Create a Graph in Excel
      How to
      Show Hidden Columns in Microsoft Excel & Unhide Column A
      How to
      Fix Ethernet Doesn't Have a Valid IP Configuration on Windows
      3 Easy Ways to Freeze Panes to Lock Columns or Rows in Excel
      Advertisement

      References

      1. ↑ https://support.microsoft.com/en-us/office/date-and-time-functions-reference-fd1b5961-c1ae-4677-be58-074152f97b81
      2. ↑ https://support.microsoft.com/en-us/office/datedif-function-25dba1a4-2812-480b-84dd-8b32a451b35c
      3. ↑ https://support.microsoft.com/en-us/office/date-function-e36c0c8c-4104-49da-ab83-82328b832349
      4. ↑ https://support.microsoft.com/en-us/office/calculate-age-113d599f-5fea-448f-a4c3-268927911b37
      5. ↑ https://support.microsoft.com/en-us/office/year-function-c64f017a-1354-490d-981f-578e8ec8d3b9
      6. ↑ https://support.microsoft.com/en-us/office/calculate-age-113d599f-5fea-448f-a4c3-268927911b37
      7. ↑ https://support.microsoft.com/en-us/office/calculate-age-113d599f-5fea-448f-a4c3-268927911b37
      8. ↑ https://support.microsoft.com/en-us/office/yearfrac-function-3844141e-c76d-4143-82b6-208454ddc6a8
      9. ↑ https://support.microsoft.com/en-us/office/calculate-the-difference-between-two-dates-8235e7c9-b430-44ca-9425-46100a162f38
      1. ↑ https://support.microsoft.com/en-us/office/calculate-the-difference-between-two-dates-8235e7c9-b430-44ca-9425-46100a162f38
      2. ↑ https://support.microsoft.com/en-us/office/calculate-age-113d599f-5fea-448f-a4c3-268927911b37
      3. ↑ https://support.microsoft.com/en-us/office/date-function-e36c0c8c-4104-49da-ab83-82328b832349
      4. ↑ https://support.microsoft.com/en-us/office/date-function-e36c0c8c-4104-49da-ab83-82328b832349

      About This Article

      In other languages
      Thanks to all authors for creating a page that has been read 602,323 times.

      Is this article up to date?

      Advertisement