In Excel, it is possible to calculate age from Birthday/Date of Birth. Just like we would figure out someone’s age by subtracting their birthdate to the current date, Excel uses a combination of formulas, as per below:
Let’s break down the formula:
- The TODAY() function returns today’s date
- The ‘birthdate’ variable is the birthday value we want to calculate the age based on
- The YEARFRAC() function calculates the number of days between 2 dates as a year function
- Finally, the INT() function rounds the number down to the next lowest integer value
Note: You need to ensure the birthdate is a number value formatted as a date, the formula will not work if it is stored as a text.
Below is an example of how to calculate the age with these functions:
Sophie was born on the 13th of December 1991, and between today (in our case 9th of May 2017) and her birthday there are 25.4055555… years. As we never mention someone’s age being “25.4”, the INT() function rounds the number down to the closest integer, which is 25.
In some particular cases, we might want to calculate an age based on a specific date, and not today’s date. In this case, we simply need to replace TODAY() either by referencing a cell containing the date or by using a DATE() formula.
In the above example, we want to know how old Sophie was when she graduated high-school (14 July 2009). There are two methods to calculate it, the first one is by replacing TODAY() by the cell containing the date and the second method is by inputting the date directly in the formula (DATE(year,month,day)).
In some other cases, we might want to determine the age of a person/group of people to be able to categorize them. In this case, we can add an IF() function to the formula. This option can be used to get a list of minors from a group of people or determine the age group of a person.
In the above example, we are finding the Seniors amongst a group of people.
We have mentioned this combination of formulas to calculate someone’s age, but it can also be used to calculate any period. For example, how long has person X been working for this company? Or for how many years has client X been loyal to this business?
Bonus: If You’d like to learn about how Date and Time work in Excel + All Excel Date & Time functions, you can watch our Excel Date & Time Masterclass on Youtube!
You can also download our Excel Date & Time functions Cheat sheet using the form below!
As alway, please leave a comment below if you have any queries/comment, we are always glad to read your feedback!
Did you find this useful? Why not share the love and tell your friends on the different social media platforms.
- Learn all about the new XLOOKUP Function in Excel! - September 27, 2019
- FOR EACH Loop in Excel VBA - December 15, 2018