Calculate Age using Formula Column

| Hamza Ali

CALCULATE AGE IN DATAVERSE WITHOUT PLUGIN OR WEB RESOURCE

Calculating age from Date of Birth in Dataverse had to be done using either a plug-in or a JS web resource but it has been made easier with the new formula data type. This feature allows you to create a calculated field based on the data in other fields. With the formula data type, you can calculate age in a model-driven app without the need for a JavaScript web resource or a plugin.

In this post, we will learn how to calculate age in

  1. "Year Month Days" format e.g. “0 years 5 months 12 days” and
  2. "Years" format e.g. “12 Years”

Let’s say there is a requirement that if age is less than 1 year then the age format should be like the first point mentioned above otherwise the age format should look like the second point.

Here are the steps to achieve this task:

1. Create a calculated column to get today's date. In the calculated column, use the NOW() function. Keep in mind that formula data type does not support the NOW() function, so a calculated column is required. Another benefit of using this field is that the calculated field job runs OOB daily hence the formula field will always be updated whenever this field is updated.

2. Create a formula column. Use the following formula in the text box:

If(DateDiff('Date custom','Calc NOW',TimeUnit.Days) < 365,Concatenate("0 years " , Int(DateDiff('Date custom','Calc NOW',TimeUnit.Days)/30), " Months ", Int(Mod(DateDiff('Date custom','Calc NOW',TimeUnit.Days),30)), " days"),Concatenate(DateDiff('Date custom','Calc NOW',TimeUnit.Years),"Years"))

This formula checks if the difference between the birthdate and today's date (Calc NOW) is less than 365 days. If so, it's concatenating a string of "0 years" with the number of full months and days between the two dates. If the difference is greater than 365 days, it concatenates the number of full years between the two dates with "Years" string.

Here are the key elements of the formula:

• DateDiff('Date custom','Calc NOW',TimeUnit.Days) - This calculates the difference in days between the 'Date custom' field (which would be the birthdate) and the 'Calc NOW' field (which is the calculated field for today's date).

• < 365 - This compares the difference in days between the birthdate and today's date to 365 days. If the difference is less than 365 days, the first part of the formula is used.

• Concatenate("0 years " , Int(DateDiff('Date custom','Calc NOW',TimeUnit.Days)/30), " Months ", Int(Mod(DateDiff('Date custom','Calc NOW',TimeUnit.Days),30)), " days") - This concatenates the string "0 years " with the number of full months between the birthdate and today's date (using integer division) and the number of full days between the birthdate and today's date (using the modulo operator).

• Concatenate(DateDiff('Date custom','Calc NOW',TimeUnit.Years), "Years") - This concatenates the number of full years between the birthdate and today's date with the string " Years"

Note that this feature is still in preview mode and it may change or not work as expected. It is recommended to test the feature on a non-production environment before applying it to your production environment.

Formula type field saves time and effort. Since there is no custom code required so it is easier than the plugin or web resource, not to forget the effort to register and debug a plug-in is also saved!

Join us next time, as we continue our journey of learning canvas apps.Click here to learn more about Imperium's Power Apps Services. We hope this information was useful, and we look forward to sharing more insights into the Power Platform world.


M
Chief Architect, Founder, and CEO - a Microsoft recognized Power Platform solution architect.

About The Blog

Stay updated with what is happening in the Microsoft Business Applications world and initiatives Imperium is taking to ease digital transformation for customers.


More About Us

We provide guidance and strategic oversight to C-Suite and IT Directors for on-going implementations. Feel free to give us a call.

1 331 250 27 17
Send A Message

Ready to Start?

Get a personalized consultation for your project.

Book a Meeting