Compute Age from Dates

In this post, I answer a question from Emmanuel who asked how to compute the age when given a list of birth dates. We want the solution to be dynamic, so that it is easy to update the age calculation going forward. Since this is Excel, there are many ways to accomplish this. In this post, I’ll demonstrate how to use a Get & Transform query.

Objective

Here is the basic idea. We have a table of dates, as shown below.

We want to compute the age of each person in years. But, going forward, we want it to be super easy to update the age calculation based on the current date. I’ve created a short video as well as a full written narrative with all of the details for reference.

Video

Details

We’ll accomplish this with a Get & Transform query. We’ll walk through each of the following steps:

• Get the table into Power Query
• Add an age column and transform it to years
• PivotTable to create the age bands

Let’s do this.

Note: The steps below are presented with Excel for Windows 2016. If you are using a different version of Excel, please note that the features presented may not be available or you may need to download and install the Power Query Add-in.

Get the table into Power Query

The first step is to get the data into Power Query. We do this by clicking any cell within the table and selecting Data > From Table/Range from the Get & Transform ribbon group. The Query Editor opens as shown below.

And, this step is complete!

Add an age column and transform it to years

Next, we need an age column. Surprisingly, this part can be tricky if you want total precision for all cases, including all ages, leap years, and leap day birthdays. So, what I’ll do is demonstrate a way that works in most cases because it is really easy and provides a good approximation. But, if you need total precision, and want to handle leap day birthdays etc, download the sample file at the end of this post where I have provided another sheet with the details for that.

The first step in our simplified version is to select the Birthdate column, as shown below

Next, we click the Add Column > Date > Age command. Now we have a new Age column that shows the duration between the current date/time and the Birthday column, as shown below.

Note: clicking Home > Refresh Preview updates the Age values. This is good to know because it means going forward, all we need to do is Refresh and the current age values are computed.

We can now choose how we want the Age column displayed. In our case, we want years, so we click the Transform > Duration > Total Years command. The resulting formula actually divides the number of elapsed days by 365. If we want to factor in leap years, then, we can edit the default formula by changing the denominator from 365 to 365.25.

The result is a decimal value. We’d like to round the number down to the nearest integer, so, we click the Transform > Rounding > Round Down command. The resulting Age column is shown below.

With our transformations complete, we simply Home > Close & Load To … to a new worksheet. The results table is loaded into Excel as shown below.

When we want to update the Age calculations, we just right-click the results table and select Refresh.

But, wait, there was one more question from Emmanuel, which was how to group them into age bands. For that, we can use a PivotTable.

PivotTable to create the age bands

To group these customers into age bands, we can use a traditional PivotTable. First, we select any cell within the results table and select Insert > PivotTable.

Next, we insert the Age, First, and Last fields into the Rows layout area. This isn’t quite what we want, but, we are getting close. The updated report is shown below for reference.

Now, to do the groups. We select any age value cell (such as 2, 7, 12, or 18). Then, we select the PivotTable > Group Selection command. The resulting Grouping dialog is shown below.

We can go with the defaults, or, define our own starting, ending, and group by values. Clicking OK creates the corresponding groups, as shown below.

If desired, we can apply some cosmetic updates to clean up the report. For example, we could go into the Last name Field Settings dialog, and select Layout & Print > Show item labels in tabular form. We could also click PIvotTable Tools > Subtotal > Do Not Show Subtotals.  We could uncheck the PivotTable Tools > Row Headers checkbox. We could select PivotTable Tools > Grand Totals > Off for Rows and Columns. We could select all age groups for custom formats as well. For example, we hover the mouse over the top cell border of the first age group 2-11, and click when the pointer turns to a down arrow. With all age groups selected, we can apply any format, for example, we could apply our favorite Cell Style. Finally, we could toggle off the display of the +/- buttons by clicking the PivotTable Tools > +/- Buttons command. And we could type Last into the Row Labels cell. The updated report is shown below.

More Precision

The method above works for most birthday/today combinations, but not all. For example, leap day birthdays. So, I’ve included an additional query in the sample file below, along with the corresponding Excel formulas, in case you need that level of precision.

In essence, the formulas factor in leap day birthdays (Feb 29th), and if the current year is not a leap year, it treats the birthday as 2/28 so that the age advances on 2/28. But, when the current year is a leap year, then, the age won’t advance until the current date is also 2/29.

If you have any other fun ways to compute the Age values, have any suggestions for improving my formulas, or, find any birthday/today combinations where my formulas break (provide inaccurate results), please let me know so I can update them accordingly. Thanks!

• Sample file: AgeFromDate.xlsx