Duration Calculated Field shows minutes not days

(0) Share

Report

Posted on by Nicole L

525

I have created a duration calculated field, it shows the number of day between the start and end date

The "Total Duration" field shows the correct number of day but not the correct field label. It show "minutes" instead of "days"

It appears to be a Microsoft bug according to https://community.dynamics.com/crm/f/microsoft-dynamics-crm-forum/234815/calculated-duration-field

Anyone know any work around or should I raise a MS support ticket? Ideal it can show xx day xx hours xx minutes in that duration field

I have the same question (0)

All responses (9)

Answers (1)

Suggested answer

cloflyMao 25,210 on at

Like (0)

Report

Hi NLuk,

I'm curious about how did you build Total Duration field.

For me, I set the field to single line text, and conbine calculated days duration and days string with CONCAT function, it works well.

A custom entity called Book:

In addition, what "Ideal it can show xx day xx hours xx minutes in that duration field" means ?

From all supported calculated function,

https://docs.microsoft.com/en-us/dynamics365/customerengagement/on-premises/customize/define-calculated-fields#calculated-field-functions-syntax

it seems that there is no function to get date difference with day hours and minutes all available as return type(date and time),

you should calculate it with own javascript function to achieve such format.

function formattedDate(executionContext) {

var formContext = executionContext.getFormContext();

var date1 = formContext.getAttribute("new_publish_date").getValue();

var date2 = formContext.getAttribute("new_release_date").getValue();

dateDiffInDate(date1, date2);

}

function dateDiffInDate(date1, date2) {

var delta = Math.abs(date2 - date1) / 1000;

// calculate (and subtract) whole days

var days = Math.floor(delta / 86400);

delta -= days * 86400;

// calculate (and subtract) whole hours

var hours = Math.floor(delta / 3600) % 24;

delta -= hours * 3600;

// calculate (and subtract) whole minutes

var minutes = Math.floor(delta / 60) % 60;

delta -= minutes * 60;

alert(days + " days " + hours + " hours " + minutes + " minutes");

}

https://stackoverflow.com/questions/13903897/javascript-return-number-of-days-hours-minutes-seconds-between-two-dates

Regards,

Clofly

Was this reply helpful? Yes No
ashish12 3,079 on at

Like (1)

Report

docs.microsoft.com/.../define-calculated-fields

Was this reply helpful? Yes No
Nicole L 525 on at

Like (0)

Report

Hi Clofly,

The Total Duration field is a custom field with the field type "duration", the "minute" displayed in the field is OOTB that Microsoft did. The 2nd screenshot provided in the original post shows the formula I used for that field.

According to the other post in the community, other people get "days" by default for a "DIFFINDAYS". I just wonder is there any other OOTB configuration or formula that determine the unit in the calculated field.

DIFFINDAYS (date and time, date and time) Returns the difference in days between two Date and Time fields. If both dates and times fall on the same day, the difference is zero. Whole Number

Was this reply helpful? Yes No
cloflyMao 25,210 on at

Like (0)

Report

Hi NLuk,

1. As all available Calculated field functions are mentioned in my post link, there are 3 return Data type

> Date and Time

> Whole Number

> String

From your screenshot, your're building the field in PowerApps,

for me, I usually create it in CRM system > customization directly,

2. As the DIFFINDAYS function definition, this function will return a Whole Number data type,

which means by default we could only set the calculated field type to Whole Number or Decimal Number to save calculated value in CRM setting,

we need customization to give the calculated data unit manually, calculated function or formula won't detect unit by itself.

So your unit determination is actually controlled by PowerApps designer, and the issue of minutes would be a bug.

3. You could take CONCAT function in my screenshot as workround.

Regards,

Clofly

Was this reply helpful? Yes No
Nicole L 525 on at

Like (0)

Report
Hi Clofly,

It appears to be a recent change by Microsoft that whenever I open the CRM System > Customization > Solution, it redirect to the PowerApps Solution by default.
interesting the "duration" field type is an option to be chosen on it own under Whole number (ref screenshot). If I look at that field in the class interface, it still shows the data type as Whole Number and field type as Duration.

Calculated field that use the DIFFINDAYS DOES display the unit by default and it's the same behavior in both Unified Interface and Classic.

For now, I'll just do the workaround and use a text field to concatenate the whole number and the unit to bypass the bug.

Thanks

Was this reply helpful? Yes No
Verified answer

cloflyMao 25,210 on at

Like (0)

Report

Hi NLuk,

Thanks for sharing this change and new customization feature that Whole Number has more specific sub types.

Regards,

Clofly

If you had found the answer resolved your issue, please kindly mark as verified, it would be greatly appreciated.

Was this reply helpful? Yes No
Nicole L 525 on at

Like (0)

Report

I've done more testing and notice that the calculated field for duration actually showing the unit correctly on the view but not on the form. Also if I use DIFFINMINUTES, it can shows the unit correctly in - minutes, hours or days

For now I've hide the duration field on the form to avoid confusion to the end user and just show the DIFFINMINUTES duration field on the view.

Was this reply helpful? Yes No
cloflyMao 25,210 on at

Like (0)

Report

Hi NLuk,

Thanks for feedback.

Currently my environment not upgrades to the version that you're working with, while I'm stil in legacy client customization interface so I couldn't try to reproduce your issue.

Indeed, the field data unit detection in modern customization is a new feature with wave 2 release, and there would be some potential usability issues.

https://docs.microsoft.com/en-us/power-platform-release-plan/2019wave2/microsoft-powerapps/modern-solution-explorer-default-customization-experience

You could open a support ticket in powerapps admin center to submit any issue you met.

Regards,

Clofly

Was this reply helpful? Yes No
Suggested answer

Ehsan Yarahmad 5 on at

Like (1)

Report

Just change Diffinday To DiffInMinutes

when using Duration all data Create by Minutes

Was this reply helpful? Yes No