Edit Article

How to Calculate Age on Excel

Community Q&A

You can calculate age on Excel for a many applications by combining an undocumented function and the date format for cells. Microsoft Excel internally stores dates as serial numbers, which is the number of days a date is from Jan. 1, 1900. The DATEDIF function will compare the difference between two specified dates, which you can use to quickly determine someone's age.


  1. 1
    Create a "Name" column. It doesn't have to have this label, but this is the column that will identify each person you are calculating the birthday for.
  2. 2
    Create a "Birthday" column. This column will contain each birthday as a separate line.
    • You don't need to use this for birthdays specifically. You can use any starting point, such as "Shipping Date," "Purchased Date," etc.
  3. 3
    Enter the birthdays using a common format. Make sure each birthday is entered using the same format. If you're in the US, use MM/DD/YYYY. If you're elsewhere, use DD/MM/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.
  4. 4
    Create an "Age" column. This column will display the age for each entry after you enter the formula.
  5. 5
    Select the first empty cell in the "Age" column. This is where you'll be entering the formula to calculate birthdays.
  6. 6
    Enter the formula for calculating the age in years. Type the following formula, which assumes that the first birthday is listed in cell B2:[1]
    • =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.
  7. 7
    Click and drag the square in the bottom-right corner of the cell down. This will apply the same formula to each line, adjusting it accordingly so that the correct birthday is calculated.
  8. 8
    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.
  9. 9
    Modify the formula to calculate exact age in years, months, and days. If you want a more detailed age, you can have Excel calculate the exact age in years, months, and days. This uses the same basic formula as detailed above, but with more arguments so that you get the exact age:[2]
    • =DATEDIF(B2,TODAY(),"Y")&" Years, "&DATEDIF(B2,TODAY(),"YM")&" Months, "&DATEDIF(B2,TODAY(),"MD")&" Days"

Community Q&A

Add New Question
  • Does the formula to calculate age work for a person born in 1929 or earlier?
    wikiHow Contributor
    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.
Ask a Question

Sample Calculator



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

Article Info

Categories: Microsoft Excel

In other languages:

Español: calcular la edad en Excel, Italiano: Calcolare l'Età in Excel, Português: Calcular Idades no Excel, Русский: рассчитать возраст в Excel, Deutsch: In Excel das Alter berechnen

Thanks to all authors for creating a page that has been read 350,668 times.

Did this article help you?