Get age in years and month from birthdate

[Rushil_15]

Hello,

I am working on one data set where I have birthdate and I am looking to get AGE in years and months. using power query I was able to get the age in the year but is there any way to get both? Thanks in advance

[ichavarria · Accepted solution]

Thanks for providing the additional information. Based on that, you can modify the formula provided earlier to calculate the age in years and months as follows:

= let

birthdate = [birthdate],

now = DateTime.LocalNow(),

ageInDays = Duration.Days(now - birthdate),

ageInYears = Number.RoundDown(ageInDays / 365),

ageInMonths = Number.RoundDown((ageInDays - (ageInYears * 365)) / 30),

ageText = Text.Combine({Text.From(ageInYears), " years, ",

Text.From(ageInMonths), " months"})

in

ageText

This formula first calculates the duration between the birthdate and the current date in days. It then calculates the age in years by dividing the duration by 365 and rounding down to the nearest whole number. Next, it calculates the age in months by subtracting the number of years from the total duration in days, dividing by 30, and rounding down to the nearest whole number. Finally, it combines the age in years and months into a single text string.

To add this formula as a custom column in Power Query for the "master_serviceplan" table:

1. Open the "master_serviceplan" table in Power Query Editor.
2. Click the "Add Column" tab in the ribbon.
3. Click "Custom Column" to open the "Add Custom Column" dialog box.
4. Enter "Age in Years and Months" as the new column name.
5. Paste the formula provided above into the "Custom Column Formula" box.
6. Click "OK" to create the new column.

Now you should see a new column in your "master_serviceplan" table that displays the age in years and months for each row.

Best regards, 

Isaac Chavarria

If this post helps, then please consider Accepting it as the solution and giving Kudos to help the other members find it more quickly.

[Rushil_15]

Thank you so much 

[ichavarria · Accepted solution]

You are welcome @Rushil_15.

If my solution worked, then please Accept it as the solution and give Kudos to help the other members find it more quickly.

Best regards, 

Isaac Chavarria

If this post helps, then please consider Accepting it as the solution and giving Kudos to help the other members find it more quickly.

[ichavarria]

Hi @Rushil_15,

With the limited information you provided, this is one way you could do it:

1. Create a new column in Power Query by clicking on the "Add Column" tab, and then selecting "Custom Column."

2. In the "Custom Column" dialog box, enter a name for the new column, such as "Age in Years and Months."

3. In the "Custom Column" dialog box, enter the following formula to calculate the age in years and months:

= Duration.FromDays(Number.From(DateTime.LocalNow())-Number.From([Birthdate])) / #duration(365,0,0,0) & " years, " & Duration.FromDays(Number.From(DateTime.LocalNow())-Number.From([Birthdate])) / #duration(30,0,0,0) & " months"

This formula calculates the duration between the birthdate and the current date, and then converts it to years and months. The "#duration" function is used to define the length of a year and a month.

4. Click "OK" to create the new column.

Now you should see a new column in your Power Query table that displays the age in years and months for each row.

Best regards, 

Isaac Chavarria

If this post helps, then please consider Accepting it as the solution and giving Kudos to help the other members find it more quickly.

[Rushil_15]

Hello,

Thanks for your time and quick follow-up. Below is the screenshot of the dataset (hiding because some have PII's). but the table name is master_serviceplan. 

Let me know if that works!

[ichavarria · Accepted solution]

Thanks for providing the additional information. Based on that, you can modify the formula provided earlier to calculate the age in years and months as follows:

= let

birthdate = [birthdate],

now = DateTime.LocalNow(),

ageInDays = Duration.Days(now - birthdate),

ageInYears = Number.RoundDown(ageInDays / 365),

ageInMonths = Number.RoundDown((ageInDays - (ageInYears * 365)) / 30),

ageText = Text.Combine({Text.From(ageInYears), " years, ",

Text.From(ageInMonths), " months"})

in

ageText

This formula first calculates the duration between the birthdate and the current date in days. It then calculates the age in years by dividing the duration by 365 and rounding down to the nearest whole number. Next, it calculates the age in months by subtracting the number of years from the total duration in days, dividing by 30, and rounding down to the nearest whole number. Finally, it combines the age in years and months into a single text string.

To add this formula as a custom column in Power Query for the "master_serviceplan" table:

1. Open the "master_serviceplan" table in Power Query Editor.
2. Click the "Add Column" tab in the ribbon.
3. Click "Custom Column" to open the "Add Custom Column" dialog box.
4. Enter "Age in Years and Months" as the new column name.
5. Paste the formula provided above into the "Custom Column Formula" box.
6. Click "OK" to create the new column.

Now you should see a new column in your "master_serviceplan" table that displays the age in years and months for each row.

Best regards, 

Isaac Chavarria

If this post helps, then please consider Accepting it as the solution and giving Kudos to help the other members find it more quickly.