How to convert a date of birth to an age without an expression using Microsoft Excel Power Query

You don’t need an expression at all to calculate someone’s age. Use Power Query to simplify the process.

Calculating age requires a bit of specialized knowledge. Any expression you use must understand date arithmetic and accommodate leap years. Years ago, the expression was much more difficult, but the .xlsx format supports the Excel YEARFRAC() function, which makes calculating age much easier.

On the other hand, you can skip it all and use Excel Power Query instead. Power Query adds the calculation automatically — you don’t have to know the expression. In this tutorial, I’ll show you how to use YEARFRAC() at the sheet level and then how to use Power Query to calculate age.

I’m using Microsoft 365 desktop on a Windows 10 64-bit system. Excel Power Query is available through version 2010. YEARFRAC() is available only in all .xlsx versions. Excel for the web doesn’t support Power Query.

SEE: Windows, Linux, and Mac commands everyone needs to know (free PDF) (TechRepublic)

How to use an expression

This article is really about using Power Query, because you don’t need an expression at all to calculate someone’s age. But in the interest of being comprehensive and offering an alternative for users not interested in using Power Query, this section will use an expression at the sheet level.

There are a number of expressions that calculate age, but if precision matters, use the following:

=YEARFRAC(dob,NOW())

where dob is the date of birth. Excel’s YEARFRAC() function calculates the fraction of the year represented by the number of whole days between two dates using the form

YEARFRAC(start_date, end_date, [basis])

The optional basis argument identifies the type of day count to use, which requires a bit of explanation:

0 or omitted US(NASD) 30/360

1 Actual

2 Actual/360

3 Actional/365

4 European/360

For our purposes, we’ll omit the optional basis argument, so enter the following function into D3 of the example sheet shown in Figure A and then copy to the remaining cells:

=YEARFRAC(C3,NOW())

As you can see, the function returns a decimal value that tells us how many days into the next birth year each person is. For example, NOW() returns a date in May 2022, Jim and Jordan just had a birthday, Delia is almost 64.5, Joy has a birthday coming up soon and the function managed Missy’s leap year birth.

Figure A

This function is easy to use if you know about it. The biggest obstacle is knowing which argument to use: For the most part, the way you’re using the data will determine that. Now let’s use Power Query, which takes the guesswork out of your hands.

How to use Excel Power Query

To avoid coming up with an expression that returns age, turn to Excel Power Query. The first step is to get the data into Power Query as follows:

1. Click anywhere inside the data set.
2. Click the Data tab and then choose From Table Range in the Get & Transform Data group.
3. If the source data is not in a Table object, Excel will prompt you to convert the data range into a Table object. Check the My Table Has Headers option and click OK. Excel will load the data into Power Query (Figure B).

Figure B

Once the data, including the date of birth, is in Power Query, you can add an age column as follows:

1. Select the date of birth column. In this case, that’s DOB. To select it, simply click the header cell.
2. Click Add Column.
3. In the From Date & Time group, click the Date dropdown.
4. Choose Age from the dropdown (Figure C).

Figure C

Figure D

As you can see in Figure D, the default format doesn’t accommodate the age values we expected. To change the format, select the new Age column and do the following:

1. Click Transform.
2. In the Date & Time Column group, click Duration.
3. From the dropdown, choose Total Years (Figure E).

Figure E

The Age column now displays the age as a whole number. The decimal values represent the fraction of the year until the next birthday. If you like, you can round the values as follows:

1. Select the Age column.
2. Click Transform.
3. In the Number Column group, click the Rounding dropdown.
4. Choose Round Down.

Figure F shows the results of rounding down the age values. It’s doubtful that you’d want to round up, but that is an option.

Figure F

At this point, you can load the data, including the rounded age values into Excel, where you can use them as you normally would any data set in Excel. To do so, click the File tab and choose Close & Load in the Close group. Figure G shows the results in a new Excel sheet.

Figure G

The one thing you lose is the decimal value portion of the age values. If you need that, don’t round down in Power Query.