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?
dateDiff in Onprem in a form
+7
Userlevel 6
+22
Hi,
The reason you are seeing this error is because there is no runtime function called dateDiff. Try dateDiffDays.
The reason you are seeing this error is because there is no runtime function called dateDiff. Try dateDiffDays.
+7
Then how do you get it to display in years and months? The client does not want the value to display in days.
Userlevel 6
+22
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.
+7
Thank you!
+7
Can you explain the %12? I assume that its dividing but why use the % vs / ?
+7
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.
Userlevel 6
+22
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.
+7
Thank you! I tested and this works.
Reply
View our Community Site Map
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.