There are a number of ways to use formulae to calculate a person’s Age from their Date of Birth (D.O.B.) in Excel but I just want to share my preferred and always used option below.
If the Date of Birth was in cell B2, the formula to calculate age in years goes as follows:
=INT((TODAY()-B2)/365)
This formula takes today’s date TODAY() and minuses the date in the DOB cell. This leaves us with the number of days between the two dates. We then divide this number by 365 to get the numbers of years.
At this point we have the number of years as a decimal number. I may be 46.3 years old but I tell people I’m 46.
The =INT part of the formula does this rounding down – it rounds the number down to an integer, a whole number.
Things To Consider
There is a slight issue to using this formula, it doesn’t consider leap years precisely. For example, if someone was born on February 29 and today is February 28, the formula will make a person one day older. This is a slight issue – in the vast majority of cases the formula works as you would want.
You could divide the number of days between TODAY and the DOB by 365.25, since every fourth year has 366 days, but then raises a potential risk the other way. For example, if you are calculating the age of a child who hasn’t yet lived through a leap year, dividing by 365.25 produces a wrong result.
Conclusion
The =INT((TODAY()-B2)/365) formula works in the vast majority of cases and it’s the approach I always use to quickly and simply calculate age from date of birth. Give it a go and let me know below if you have any thoughts.
Leave a Reply