How to Calculate Age from a Birthdate in Excel 2013

Excel has a lot of tools that can help you calculate values based on data that you’ve entered. You’ve probably had to add up values in Excel before, or maybe even calculate an average, but there is even more that you can do with numerical values that you have entered into a spreadsheet.

One other thing that you can do is calculate someone’s age based on a birthdate that’s been entered into a cell. Our tutorial below will show you the formula that lets you take a birthdate and compare it to the current date to see how old someone is.

 

Quick Summary – How to Determine an Age from a Birthdate in Excel 2013

  1. Open Excel 2013.
  2. Type a birthdate into a cell in the format MM/DD/YYYY (if in the United States) or in the format DD/MM/YYYY if your country uses that format.
  3. Type =DATEDIF(XX, TODAY(), “Y”) then press Enter on your keyboard. Change the “XX” part of the formula to the cell containing the birthdate.

For more information, including pictures, continue reading below for our expanded how-to guide on calculating age in Excel.

 

Expanded – How to Calculate Age in Excel 2013

The steps in this article were performed using Microsoft Excel 2013, but will also work in most other versions of Excel. This guide is focused specifically on calculating the age in years based on the entered birthdate, but we will include some modifiers for the formula at the end of the article if you want to calculate age in days or months, or if you want to display the age in years, months, and days.

Step 1: Open Excel 2013.

 

Step 2: Enter a birthdate into a cell using the format MM/DD/YYYY (United States) or DD/MM/YYYY if your country uses that date format instead.

how to determine age with a formula in excel

 

 

Step 3: Type =DATEDIF(XX, Today(), “Y”) into the cell where you want to display the age, then press Enter on your keyboard. Be sure to replace the “XX” in the formula with the cell location containing the birthdate.

how to calculate age in excel 2013

 

 

Additional Info

  • If you enter the birthdate in the format specified above, Excel should be able to determine automatically that it’s a date. However, if you are using a different date format that Excel seems to be struggling with, or if the data is being formatted in some other way, then right-click on the cell with the birthdate, choose Format Cells, then choose the Date option at the left side of the window and choose the correct format based on how your data is entered.
  • If you wish to display the age in months, then modify the formula to be =DATEDIF(XX, TODAY(), “M”)
  • If you wish to display the age in days, then modify the formula to be =DATEDIF(XX, TODAY(), “D”)
  • If you wish to calculate what someone’s age was on a specific date, either in the past or in the future, then modify the formula to be =DATEDIF(XX, “MM/DD/YYYY”, “Y”)
  • Note that this formula calculation will not work if the starting date occurs before 01/01/1900.

 

The sort feature in Excel is another useful tool, and you can use it to sort columns with dates. Find out how to sort by date in Excel if you have a need to do so.

solveyourtech.com newsletter
vintage t rex t-shirt

Disclaimer: Most of the pages on the internet include affiliate links, including some on this site.