Convert Dates into Ages

If you need to calculate the age in years based on a list of birth dates in Excel, fear not! In this short tutorial, we’ll cover a few functions that will help, as well as a method to count the number of rows in each age group. Specifically, we’ll explore the YEARFRAC, TRUNC, DATEDIF, and COUNTIFS functions. So let’s get started!

Video

Step-by-step guide

Let’s explore these functions by using a few specific exercises.

Exercise 1: Using YEARFRAC & TRUNC

Our objective is to compute the age in years between the birth date and the “as of” date as shown below.

To do this, we’ll start with the YEARFRAC function. This function computes the fractional portion of a year (or years) between two dates. The basic function signature is:

=YEARFRAC(start_date, end_date, [basis])

There are three arguments: the start_date, end_date, and optionally basis which provides options for the count (such as 365, actual, 360, and so on). In our case, our start date is the birth date, the end date is our “as of” date, and the basis is 1 for actual days.

Now, this will return the number of years between the two dates, but also the fractional portion of a year.

To get rid of the fractional portion, we’ll wrap the TRUNC function around the YEARFRAC function like this:

=TRUNC(YEARFRAC(start_date, end_date, 1))

This truncates the year portion, leaving us with just the age in years:

Now let’s take a look at an alternative.

Exercise 2: Using DATEDIF

In this exercise, we’ll use the DATEDIF function instead of the TRUNC/YEARFRAC combo to calculate the age in years. This function is considered deprecated, so it’s not officially documented in Excel. In practice, it feels safer to use YEARFRAC and TRUC, but, it’s cool knowing our options.

The basic syntax to compute the number of years between two dates is:

=DATEDIF(start_date, end_date, "y")

It returns the same results as the previous formula:

Now let’s count the number of rows for age ranges we define.

Exercise 3: Counting Age Groups

We’ll use the COUNTIFS function to count the number of rows for each age range we define. We use the basic formula syntax as follows:

=COUNTIFS(age_column_ref, ">="&[From], age_column_ref, "<="&[To]) 

And the results are displayed in the Count column below:

This will count the number of rows where the age column is greater than or equal to the From age and less than or equal to the To age.

Conclusion

I hope this post was helpful for computing ages from a list of dates, and then counting the number of ages that fall within pre-defined ranges.

If you have any alternative approaches, suggestions, or questions, please share by posting a comment below … thanks!

Sample File