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

In Microsoft Excel, you don’t need expressions to calculate someone’s age – use Power Query to simplify the process.

Calculating age requires a bit of specialized knowledge. Any expressions you use must understand date arithmetic and be suitable for leap years. Years ago, expressions were much more difficult, but the .xlsx format supports the YEARFRAC() function in Excel, making age calculation much easier.

Otherwise, you can skip them all and use Excel Power Query instead. Power Query adds calculations automatically – you don’t need 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. You can download the demo file for this guide.

I am using Microsoft 365 desktop on Windows 10 64-bit system. Excel Power Query is available until version 2010. YEARFRAC() is only available in all versions of .xlsx. Excel for the web does not 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 the expression to calculate someone’s age. But for the sake of comprehensiveness and providing an alternative for users not interested in using Power Query, this section will use a sheet-level expression.

There are several age expressions, but if precision is important, use the following:

=YEARFRAC(dob,NOW())

where dob is the date of birth. Excel’s YEARFRAC() function calculates the fraction of a year expressed as an integer number of days between two dates using the form

YEARFRAC(start_date, end_date, [basis])

The optional base argument determines the type of date to use, 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 base argument, so enter the following function in D3 of the sample sheet shown in Picture 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 each person has left in the next birth year. Eg, 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, and Missy’s leap year birthday management function.

Picture A

This function is very easy to use if you know about it. The biggest hurdle is knowing which arguments to use: For the most part, how you’re using the data determines that. Now, use Power Query, taking the guesswork out of it.

How to use Excel Power Query

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

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

Figure BUT

When data, including birthdays, is available in Power Query, you can add an age column like this:

1. Select the date of birth column. In this case, it’s the DOB. To select it, simply click on the title box.
2. Click Add Column.
3. In the Date & Time group, click the Date drop-down menu.
4. Select Age from the drop-down menu (SIZE).

SIZE

Visualization

As you can see in Visualization, the default format doesn’t match the age values ​​we expect. To change the format, select the new Age column and do the following:

1. Click Convert.
2. In the Date & Time Columns group, click Duration.
3. From the drop-down menu, select Total years (Figure E).

Figure E

The Age column now displays age as an integer. The decimal values ​​represent the fraction of the year until the next birthday. If desired, you can round the values ​​as follows:

1. Select the Age column.
2. Click Convert.
3. In the Number Columns group, click the Rounding drop-down menu.
4. Select Round Down.

Figure F displays the result of rounding the age values. It’s doubtful that you want to round, but that’s 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 with any data set in Excel. To do so, click the File tab and select Close & Load in the Close group. WOOD Figure display the results in a new Excel worksheet.

WOOD Figure

One thing you lose is the decimal part of the age values. If you need that, don’t round in Power Query.