DinaF
Cloud Wanderer

dateDiff in Onprem in a form

Jump to solution

I'm trying to display the difference between 2 dates and display the results in years and months.  I created a calculated field but I keep getting an error.  Can someone help me figure this out?Error MessageError MessageCalculated Value formulaCalculated Value formula

 

0 Kudos
Reply
8 Replies
SimonMuntz
Nintex Employee
Nintex Employee

Re: dateDiff in Onprem in a form

Jump to solution
Hi,
The reason you are seeing this error is because there is no runtime function called dateDiff. Try dateDiffDays.
Reply
DinaF
Cloud Wanderer

Re: dateDiff in Onprem in a form

Jump to solution

Then how do you get it to display in years and months?  The client does not want the value to display in days.

0 Kudos
Reply
SimonMuntz
Nintex Employee
Nintex Employee

Re: dateDiff in Onprem in a form

Jump to solution

The formula would like like this:

round(dateDiffDays(Sdate,Edate)/365)+" "+"Years"+" "+round(dateDiffDays(Sdate,Edate)/30)%12+" "+"Months"

I have attached a sample Responsive form which demonstrates this formula for your testing.

Months are calculated on a 30 days so not 100% accurate but pretty close. You could always change it to 31 days.

DateDiffYM1.png

Reply
DinaF
Cloud Wanderer

Re: dateDiff in Onprem in a form

Jump to solution

Thank you!

0 Kudos
Reply
DinaF
Cloud Wanderer

Re: dateDiff in Onprem in a form

Jump to solution

Can you explain the %12?  I assume that its dividing but why use the % vs / ?

0 Kudos
Reply
DinaF
Cloud Wanderer

Re: dateDiff in Onprem in a form

Jump to solution

So I just checked your formula and it does not appear to be calculating correctly.  For example if the start and end dates are  01/01/2018 – 07/10/2019, that should show 1 Year 7 Months but your formula will show 2 Years 7 Months.

0 Kudos
Reply
SimonMuntz
Nintex Employee
Nintex Employee

Re: dateDiff in Onprem in a form

Jump to solution

Hi,

Hmmm I shouldn't have used Round() for the years as it has rounded up. I just wanted to get rid of the decimal places after dividing.  I changed the formula so that it just replaces the decimal with blank space and now it outputs correctly based on your dates. (test form attached)

replace((dateDiffDays(Sdate,Edate)/365),"([^0-9].[0-9]*)","")+" "+"Years"+" "+round(dateDiffDays(Sdate,Edate)/31)%12+" "+"Months"

Q. Can you explain the %12?  I assume that its dividing but why use the % vs / ?

A. The % is a math function called Modulo.  It finds the remainder of a division.

As we have already taken care of the years in the first bit of the formula (divide by 365) we just want to know the remaining months.

Example.  If we have 18 months and we divide by 12 we get 1.5.

18/12 will give us 1.5

but

18%12 will give us 6 (one lot of 12 with 6 remainder)

 

This may not be perfect and I am sure a more complex formula could get exact but this will take you close.

 

 

View solution in original post

Reply
DinaF
Cloud Wanderer

Re: dateDiff in Onprem in a form

Jump to solution

Thank you!  I tested and this works.

0 Kudos
Reply