| 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
- "Year Month Days" format e.g. “0 years 5 months 12 days” and
- "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.
