 Cloud Wanderer

## dateDiff in Onprem in a form

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 Message Calculated Value formula

8 Replies  Nintex Employee

## Re: dateDiff in Onprem in a form

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

## Re: dateDiff in Onprem in a form

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

## Re: dateDiff in Onprem in a form

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.  Cloud Wanderer

## Re: dateDiff in Onprem in a form

Thank you! Cloud Wanderer

## Re: dateDiff in Onprem in a form

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

## Re: dateDiff in Onprem in a form

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.  Nintex Employee

## Re: dateDiff in Onprem in a form

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. Cloud Wanderer