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
- Open Excel 2013.
- 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.
- 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. We will be using a formula, like this subtraction formula in Excel, to accomplish this.
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.
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.
Now that you know how to calculate age from a birthdate in Excel 2013, you can use this formula to find this and other types of information based on data that you might have in your spreadsheet.
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.
Matthew Burleigh has been writing tech tutorials since 2008. His writing has appeared on dozens of different websites and been read over 50 million times.
After receiving his Bachelor’s and Master’s degrees in Computer Science he spent several years working in IT management for small businesses. However, he now works full time writing content online and creating websites.
His main writing topics include iPhones, Microsoft Office, Google Apps, Android, and Photoshop, but he has also written about many other tech topics as well.