Introduction
Dates calculations in Excel can be problematic, not because the arithmetic is difficult but because the dates themselves can be corrupted as we take data from web sites, CSV files, XLSX files, PDF files, databases and more. However, let's imagine that there is no such problem as that and find out how to calculate your age using Power Query. Let's find the ages of many people, to ensure we understand the usefulness of the technique I am about to discuss.
From the Intro tab:
Read through the above intro alongside the following images to follow along with me to convert, for example, 25th August 1987 to 33.71 years.
Here is the Power Query Editor. I have already duplicated the Date of Birth column, selected the new column and then Transform, Date, Age
Having done that, you will see that I got total days as the result: more than 12,000, more than 14,000 ...
Keep that column selected ...
Now you click on Transform, Duration and choose Total Years for this example ... consider other selections in other examples.
Essentially, that's it but it probably helps to round the result since 8 decimal places is not so attractive:
There you are: everyone in the database can see their Date of Birth and their Age in the Query table.
Conclusions
You can download my file in which you can follow my steps but I find this to be a really convenient and simple technique and don't forget, when you update this file in any way, the Query will also update and reflect those changes, as with all Power Query examples.
Download the Excel file:
Duncan Williamson
1st May 2021
No comments:
Post a Comment