Using integer from another field produces erratic calculated dates

  • 1 September 2021
  • 11 replies
  • 21 views
SmashedMonkey

Hi, I have a bizarre problem concerning the dateAddMonths runtime function in a calculated field. I have a date field which is called HB-EffectiveDate and that sets the start date. I want a second date to be calculated based upon HB-Duration, a text box (set as integer), whereby the resulting date should be: HB-EffectiveDate + number of months set by varDuration (which is basically HB-Duration).

There is a checkbox HB-SinglePayment-YN, that determines if there a single payment (i.e. no months to add). So, when checked it will just return the same as HB-EffectiveDate. The formula I have for this in the calculated field is:

 

If(HB-SinglePayment-YN==True,formatDate(HB-EffectiveDate,"dd/MM/yyyy"),formatDate(dateAddDays(HB-EffectiveDate,varDuration),"dd/MM/yyyy"))

 

However, the value shown in the calculated field is completely erratic. Just adding 1 into HB-Duration sends the calculated date from 01/09/2021 to 01/10/2027, so it seems to be adding a magnitude of months instead of just 1.

I have run out of ideas. Can anyone help?

11 replies

SmashedMonkey
Sorry, this should be labelled as Nintex for SharePoint On-Premise, but I could not choose that label when I created this post?
A
Badge +8

Ur formula should work.


 


Dont use the "varDuration" .. u can just use the HB-Duration.


 


if(HBSinglePaymentYN==True,formatDate(HBEffectiveDate,"dd/MM/yyyy"),formatDate(dateAddMonths(HBEffectiveDate,HBDuration),"dd/MM/yyyy"))


 



 


 

SmashedMonkey

Thanks @Aleximo I have tried that, but I still get the same result. If I use the date (UK format) as follows: 01/09/2021


Then entering 1 to the HB-Duration, I get 11/09/2021 (it seems to add 10 days, instead of 1 month)


Entering 2 to the HB-Duration, I get 12/09/2021 (now it only adds one day)


Entering 3 to the HB-Duration, I get 13/09/2021 (it adds another day)


If I leave HB-Duration at 3 and change the HB-EffectiveDate to 02/09/2021, I then get 23/09/2021, so it adds 10 days.


If I then change the HB-EffectiveDate to 04/09/2021, I then get 13/10/2021 (a little more than 1 month). I don't suppose this might have anything to do with the American/UK date format does it? Where we in UK say dd/MM/yyyy and in the US it's MM/dd/yyyy ??

A
Badge +8

@SmashedMonkey 


 


Can u show me ur formula with a screenshot?


 


I live in germany so the time format shouldnt be a problem.


 


I tried it myself (setting up a test form) and it works with the formula i posted.


 


Its important that u use the "Benannte Steuerelemente"... u should see the difference with the missing "-".


 

SmashedMonkey

I realise that I had used DateAddDays in my original post, because I tried testing with both Months and Days, but is still gave the wrong outcomes.


I think I have found the problem. Not sure why this is the case, but I set the text box to Decimal, instead of Integer, and then had to add the sum() function to the output, so the formula that now works is this:


If(HB-SinglePayment-YN==True,formatDate(HB-EffectiveDate,'dd/MM/yyyy'),formatDate(dateAddMonths(HB-EffectiveDate,sum([HB-Duration,0])),'dd/MM/yyyy'))


Just adding the output of HB-Duration to 0 gives me the correct calculated outcome on the date.


Does anyone know what is going on here? I have never encountered this before??


Thanks.


SmashedMonkey

@Aleximo Sorry, I wasn't ignoring you.


Here is the screenshot of my formula...



Are you saying to use the tab Item properties?


 

A
Badge +8

I deleted my (last) reply because i thought it sounded "rude".. didnt mean it that way.


 


yes you should use the "item properties" and u dont have to use the sum() function.


 


if(HBSinglePaymentYN==True,formatDate(HBEffectiveDate,"dd/MM/yyyy"),formatDate(dateAddMonths(HBEffectiveDate,HBDuration),"dd/MM/yyyy"))


 


that formula should do it and if u use the "item properties" the "-" between the words should be gone.

A
Badge +8

I am a bit confused right now... "Named Controls" should work...


 


Did u renamed the controls so they are the same as the coloum name?... that would explain the "-" in the formula.


 


in my test form nintex automatically removed them:


 



 

SmashedMonkey
The column has no "-" but I did try one version with one, so maybe took the screenshot when it was there. OK, so ALL of the values in the formulae should use the Item Properties? I did try with just the one HBDuration property, but the outcome was empty, but all the others were Named Controls. I will replace all of the controls with the Item Properties and see what happens.
I noticed that using the sum() function, when the number of HB-Duration went over 9, then it gave incorrect date values again.
A
Badge +8

@SmashedMonkey 


 


Did it work?

smashed-monkey
Badge +1

Sadly, no. Using all the item properties results in an empty result. I have rechecked everything. I found that the HB-Duration control was not connected to it's SharePoint list column, and checked that the column was set to number. Even having done that, I still don't get any results unless I go back to the sum() option. I have to admit that I don't know what is causing this.


I have used dateAddDays and dateAddMonths in other forms and have not had these problems.

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings