An easy way on how to calculate the age from date of birth using Excel formula
Here we’ll look at how using the Today() function along with a less well known function, Datedif(), calculates the age from a date of birth. Even if this isn’t a pressing need of yours the example below is a handy demonstration of the usefulness of these two functions in an Excel formula to calculate age from a date of birth.
The following formula uses both these functions to achieve the desired result.
=DATEDIF(B4,TODAY(),”y”)
where “B4” is the actual cell containing the date of birth.
Here’s the formula in action.
Tip: You can also express the age as months, or even days by simply changing the “y” in the above formula to “m” or “d” respectively.
There are many ways to achieve the same result and the example above is one such way on how to calculate age from date of birth using Excel.
How Excel stores dates:
Dates and time are some of the most common types of data that people use in Excel and the way that Excel stores dates is quite different to how we would imagine.
Instead of storing a date as day, month, year (01/01/1900, for example) Excel actually allocates a serial number (which is generated from working out how many days have elapsed since the year 1900 to that date). And yes, any date before 1900, as far as Excel is concerned, just doesn’t exist! This is why if you don’t have the correct cell formatting for date data you get a number bearing no relation to the date!
Additional resources:
You can view answers to actual Excel users’ questions related to time and date on the following links:
https://www.stl-training.co.uk/post-6898-calculated-age-person-each.html
https://www.stl-training.co.uk/post-23813-calculating-ages-dates.html
For a more in-depth look at how to use dates and times in Excel:
http://support.microsoft.com/kb/214094
