Datediff in calculated fields is incorrectly calculating

(0) Share

Report

Posted on by Community Member

I am using the datediff function in the calculated fields to calculate how long a case has been open for. I am using a whole number datatype with duration format (I have also tried none format). The formula I have used is DiffInYears(createdon, Now()), but I have also tried months and hours for the datediff formula.

The result is completely off and inconsistent. For example a case that was created two months ago may show a duration of 11 hours while another one will show a duration of two minutes even though it is days old. Also, the Years option and Hours option in the datediff calculation will show two separate results, it will 2 minutes and 11 hours for the same case (the result in the field shows hours and minutes next to the field).

I suspect is trying a conversion of some sort from the UTC field.

I would much appreciate anyone's advice or assistance on this. Thank you.

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (0)

Suggested answer

Nithya Gopinath 17,078 on at

Like (0)

Report

Hi Lindsay,

Please go through the following blog.

community.dynamics.com/.../new-calculated-field-functions-in-crmonline-update-1

It says, the trick to getting UTC time, in this case, is to first create the date time field as User Local, fill in the calculation field action, and only then change the date time behaviour to Time Zone Independent. Also note that, it is possible to change the date time behavior from User Local to Time Zone Independent, but not the other way around (from the UI).

Was this reply helpful? Yes No
Nadeeja Bomiriya 6,804 on at

Like (0)

Report

Hi Lindsay61,

Can you please include a screenshot of the calculated field configuration? Also, what's the data type of the calculated field?

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (1)

Report

@Lindsay61,

If you assign a value returned by statement "DiffInYears(createdon, Now())", you will always get the difference value in terms of MINUTES, as the Duration type of Field's lowest denomination is MINUTES. It accepts value in Minutes and then converts it into HOURS and DAYS andso on.

Hence, you need to convert the Calculated formula as:

DiffInMinutes(createdon, Now()),

And assigned the return value to the Field.

I hope this helps.

Was this reply helpful? Yes No
Suggested answer

Michael Mayo 13 on at

Like (0)

Report

MS CRM is on to something, but missing this very important detail: The field needs to be deleted and recreated with the format NOT set to "duration" but rather "none"

Was this reply helpful? Yes No