The dateDiffDays" function considerations

  • 26 September 2016
  • 1 reply
  • 394 views

Userlevel 6
Badge +16

The "dateDiffDays" function will only return the difference between two dates as a positive integer. It is only designed to return the difference between two dates, not determine if one date falls before or after another.

 

To have the date difference display in this way, you will need to use a combination of some calculated values and a Form variable. Essentially you need to compile a string, which simply adds the "-" symbol in front of any difference value when Date A is less than Date B.

 

Try it doing the following:

 

1 - Add two Date/Time controls to the Form, Date A and Date B

2 - Add a calculated value control and set it to calculate the difference between the above: dateDiffDays(DateA,DateB) - Be sure to give this control a name so it can be referenced later, I simply called mine "Difference."

3 - Add a second calculated value control and set it to the following formula: If(greaterThanOrEqual(DateB,DateA),' ','-') - This will generate a negative symbol when the second date is an earlier date to the first date. Name the control (I named mine "Tag")

4 - Add a Form Variable, set it to type "String" set all recalculate options to "Yes" and then use the following formula: trim(Tag+Difference)

5 - Add a third calculated value control and just set it to log the Form variable.

 

When you preview the Form and enter some dates you should see the third calculated value control log what appears to be a negative number. Note you can hide the other calculated value controls so they aren't visible to the end user.

 

It's important to note that the above will not actually log a negative integer, just give the appearance of one. So if an actual negative integer is needed the conversion would need to be done by a workflow when the user saves the list Item or submits the task (depending on the type of Form.)

 

With some work you may also find you can do this conversion entirely within a variable, however I believe using the calculated value controls is probably a better option as it gives you an opportunity to have a look each step in the process should anything go wrong.

 

Hopefully that's helpful.


1 reply

Badge

Be careful using the dateDiffDays function. It can be just a joke sometimes. Try the following calculations on Nintex form. They both return the same result:

dateDiffDays("3/13/2023","3/14/2023") = 1
dateDiffDays("3/12/2023","3/14/2023") = 1

 

Possible Solution: Alternative formula that I came up with which works for me that you may want to give it a try:

 Round((EndDate - StartDate)/86400000, 0)

 Note: 86400000 is milliseconds in a day.

 

Best,

anho

Reply