Calculated Field - DIFFINDAYS error

(0) Share

Report

Posted on by RobinA

I'm trying to use the standard calculated field to calculate the number of days between an Opportunity Created On and Actual Close Date

DIFFINDAYS(createdon, actualclosedate)

When I try to save the calculation it says: "You can't use actualclosedate, which is of type dateTime, with the current function."

What is the problem here can anyone advise what is going wrong?

4478.error.PNG

*This post is locked for comments

All responses (6)

Answers (1)

Eric benco 320 on at

Like (0)

Report

RE: Calculated Field - DIFFINDAYS error

At some point in time, Microsoft changed the behavior of the Actual Start Date field. So @carlik suggestion will only work if you have an older version of D365.
Carlik 55 on at

Like (0)

Report

RE: Calculated Field - DIFFINDAYS error

Starting from your reply I think I have a simpler solution.

I created a date/time field on opportunity (__fixedCreatedOn), set it as DateOnly and set it as calculated field. It is setted as equal to createdon field. (so I don't create any workflow)
LuHao 40,872 on at

Like (0)

Report

RE: Calculated Field - DIFFINDAYS error

Hi Robin,

I'm glad your problem has been solved.

Please click Yes to mark the verified answer.

This will help other users quickly navigate to this theme.

Thanks. :)
RobinA 65 on at

Like (0)

Report

RE: Calculated Field - DIFFINDAYS error

Great that resolved the issue Clofly thank you very much for your time.
Verified answer

LuHao 40,872 on at

Like (2)

Report

RE: Calculated Field - DIFFINDAYS error

Hi Robin,

That because the system createdon field Behavior is set to User Local,

and your actualclosedate is set to Date Only.

However, once Behavior of a Date and Time field was set to Date Only, it can't be changed again:

https://docs.microsoft.com/en-us/dynamics365/customer-engagement/portals/behavior-format-date-time-field

To overcome this issue, you could create a on-demand workflow and a new date and time field with same Behavior as createdon, update this new field with previous actualclosedate:

Then bulk updating the new custom fields:

Finally you work with new field to calculate.

Regards,

Clofly
Suggested answer

Joe Griffin 1,254 on at

Like (0)

Report

RE: Calculated Field - DIFFINDAYS error

Hi Robin,

I tried replicating this on my side and I am getting the same issue - this is pure speculation, but the date format settings between both these fields are different, so just wondering if this could be causing the problem. As there is no way to override this behaviour, you could look at setting up a new date/time field, with the same format settings as the createdon field and then create a Flow/Business Rule to update this value every time the actualclosedate changes. This new field can then be used as part of your calculated field. You might also be able to escalate to Microsoft support for further guidance/assistance.

Hope this helps!

Thanks

Joe