Date parameters in calculated fields

[eky]

Hello everyone,
I’m trying to create a calculated field that calculates Case resolution time (format is Integer) (Date closed(P1) -Date created (P2) ). I have created the date closed field and used Workflow to automatically update it.
The problem is that the formula (datediff P1, P2) (subtract P1, P2), (subtractHours P1, P2) doesn’t return any value
I further created another case resolution field, this time in DATEtime format but it still doesn’t return results.

Is there a better function to use? Where am I making the mistake? Thanks!

[BrozTechnologies]

Take a look here:

Workflow Calculated Fields

BrozTechnologies

[eky]

Thank you for response. Yes, I used the document you linked above.
inputting this “{datediff(2016-02-01; 2016-04-22; days)}” works but I won’t be using direct dates like that. I will be using variables (other fields). In my scenario, date created is P1, date modified is P0. I’m inputting “”{datediff(P0; P1; days)}" in the calculated fields. The process audit of the workflow then shows “failed”. Is there no way to compare two fields? @pgr

[pgr]

Date problems usually boil down to differences in timezone and in locales. These are quite difficult to get right in PHP.

Maybe the kind folks at @crmspace can shed some light here?

[crmspace]

Hey,
I just tested the calculation with this workflow:

For each contact, the difference in days is being calculated correctly (like “days delta: 8”).
I switched as well the default date format of the current user, and it did not affect the workflow.

[eky]

Hello @crmspace, I tried to replicate your solution but it is not coming out right. The formula returns 0 (that field is an integer field)

. I hope the attachments can convey the information required. My version is Version 7.11.3 Sugar Version 6.5.25 (Build 344)

[crmspace]

Can you try to save the value to the description field? Maybe casting from string to int is the problem.

[eky]

It’s a live product so I can’t mess with the description field, I’ll need a new field entirely. Which field type should I create to mimic the description field? Also, is it possible to get the difference in hours rather than days? Thank you.

[eky]

I just did, it returned 0 unfortunately. That was the Cases module though, yours was the contact module. What else can I try? The process audit of the workflow shows complete

[crmspace]

Hey,
sorry for not replying faster. Do you have regular timestamps for these fields (yyyy-mm-dd hh:mm:ss)?

And you calculate hours the same way, just change “days” to “hours”:

[eky]

Yes I do, all three date fields (date created, modified and closed, I have attached a screenshot) have the format you mentioned above. What more could be the problem?

[mattp]

Did anyone ever get this resolved? The datediff is special in that it doesn’t require a format string so I guess it works with native datetime types?

Anyway to work from/to datetime types instead of strings?

My case is simple. I am taking the default (built in) Date Created datetime field and want to add 1 day to it and store the result in a custom Due Date datetime field.

I am guessing this won’t work because these functions are all trying to work with strings instead of datetimes for some reason?