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.

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.

Thank you!

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

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.

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.

Thank you!  I tested and this works.