Calculate Age on Excel

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.

Steps

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. 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. 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. Create an "Age" column. This column will display the age for each entry after you enter the formula.
5. Select the first empty cell in the "Age" column. This is where you'll be entering the formula to calculate birthdays.
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:
   • =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. 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. 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. 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:
   • =DATEDIF(B2,TODAY(),"Y")&" Years, "&DATEDIF(B2,TODAY(),"YM")&" Months, "&DATEDIF(B2,TODAY(),"MD")&" Days"

Sample Calculator

Doc:Age Calculator

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.

Related Articles

• Make a Spreadsheet in Excel
• Read an Excel Spreadsheet
• Create a Mortgage Calculator With Microsoft Excel
• Edit Data in Microsoft Excel
• Format a Cell in Microsoft Excel

Sources and Citations

See also

• Create a Sin and Cos Circle in Excel
• Add a Header or Footer in Excel 2007
• Calculate CAGR in Excel
• Use Excel for Your Finances
• Parse Data in Excel
• Create Custom Lists in Excel
• Remove Leading or Trailing Zeros in Excel
• Remove Spaces Between Characters and Numbers in Excel
• Use Vlookup With an Excel Spreadsheet
• Create a User Defined Function in Microsoft Excel

Read more ...

What links here

• Add Data to a Pivot Table
• Calculate a Monthly Payment in Excel