Formula for subtracting date and converting the defference to integers

Hello, 

Formula value uses standard C# syntax. In your case subtraction of 2 datetime fields gives you a TimeSpan, you can get the number of days in the TimeSpan by using Days property (TotalHours if you want to calculate difference in hours). 

So to get the difference between 2 date fields you can do the following formula: 

([#Date 1#] - [#Date 2#]).Days

You can assign this value to a business process parameter and them with modify data update the needed field with this parameter. 

Best regards,

Dennis

Dennis Hudson,

 Thanks for the reply. I am trying to build a business process to run every morning and that process should read the two dates and update field with the difference in those dates in days.

Can you guide with how to build such a process?

Currently it's as below and is really working. Just can't figure out how to read all the records and then update the field based on the current values of the record.

aaykay,

There are 2 options how you can do that: 

1) You can add a boolean column to your object (Something like Processed) and loop through all records, which have processed = false. After that you can read the first record in the selection with the mentioned filter, process date columns and after that set the Processed to true. You would need to loop until there is a record that has processed = false (count > 0). 

After all the mentioned operations you would need to set Processed of all records to false (one Modify data element). 

2) You can read all records as a collection, pass it to script task and operate with them in a script task. Here is some documentation on using collection: 

 https://community.bpmonline.com/questions/use-collection-parameter-script-task

https://community.bpmonline.com/questions/use-script-task-update-column-read-data-result-collection

Best regards, 

Dennis 

Dennis Hudson writes:

You can add a boolean column to your object (Something like Processed) and loop through all records, which have processed = false.

How do I loop through the records in the business process?

aaykay,

It should look similar to the screenshot below:

http://prntscr.com/nr79cf

Best regards,

Dennis

Dennis Hudson,

 When I run the process and put a constant number such as 07 or 50 or any other value, the business process updates the value in the specified field. But when I try to compute the value using the dates, it doesn't work. I am using the same formula as you mentioned earlier in the first comment (date2 - date1).Days.

Can you tell what might be going wrong?

aaykay,

Could you please specify what do you mean by 'doesn't work'? It gives you an error or the value is incorrect? In first case please give me an error message, in the second case - what dates do you input and value do you get when using the formula? Also, could you please send a screenshot of your formula and a part of process flow in which you use it? 

Dennis Hudson,

 Here are the screenshots:

1. Complete Process

2. Date formula for filled

3. Date formula if closed:

4. Final modification:

I am neither getting an error nor any other response. The integer field that I am trying to update shows value = 0, which I guess is the default value of field of type integer. 

Dennis Hudson,

 I omitted conditional flow and now I am getting the following output when I run the process for only status = Closed

The variable "DateDifference" now has value = -737,065

aaykay,

It happens because one of the date values is empty, if you fill in both date/time fields you should get a correct result. 

aaykay writes:

The variable "DateDifference" now has value = -737,065

Dennis Hudson,

Both the fields are filled. Please find the screenshot below.

aaykay,

Probably you are calculating those fields using the dates of one particular record that has one of the dates not filled in, and not for each record separately. Please write to the support@bpmonline.com for more detailed analysis. I've double checked the formula itself and it works fine, probably the issue is in the process flow.

Best regards,

Dennis  

Dennis Hudson,

 You can find the business process in the screenshots above. Even I think something is not upto the mark with process but only if I knew.

aaykay,

It's hard to determine what's wrong here from screenshots. I think it will be better to contact support.