Discover how to calculate age using Excel spreadsheets and formulas. Get the best out of Excel!
Calculating age with Excel can sound harder than you think as you have to take into account certain factors like leap years. However, we explain the different ways to calculate age that are simple and practical.
Calculate age by counting days
This is the simplest way of calculating age in Excel and what comes to mind first is to subtract your birthday from the date today. This will give us a number that we need to divide by 365 (quantity of days in a year). The formula is:
age = (actual date - birthday) / 365
However, this method has the problem of omitting leap years, which could present a certain degree of error and will not be a precise figure. Remember that to include today’s date in excel you can use the code TODAY in the cell. You can view more codes for date and time here.
Calculate age using YEARFRAC
The second formula for calculating age in Excel is to use the function of YEARFRAC. This gives you the split of the year that represents the number of complete days between the initial and final date.
age = YEARFRAC(birthday;today’s date)
With this formula the result is much more precise as the coding already takes into account the characteristics of the years between the two dates. If you would like to have the rounded-up number (age is a complete number, not in decimals) remember to use the Excel code INT:
age = INT(YEARFRAC[birthday;today’s date])
Calculate age using DATEDIF
The last formula to calculate age in Excel is by using the function DATEDIF. For this, you will need to know the initial date (birthday), the actual date (today) and the type of data that you want to collect, which in this case is years (“y”):
age = DATEDIF(birthday;today’s date;”y”)